Almost there. The formula returns the max purchase overall for all customers. So every customer has a max puchase of $232,000. If only that were true :) Ok .. I changed some of the criteria and now the formula max({totalamount}) keep(dense_rank first order by {trandate} desc) produces the exact same result as…

That works! Thanks for the help. It's a slow brain day for me today ... apparently.

Nope. I'm just dumb ;) The formula max({totalamount}) keep(dense_rank first order by {trandate} desc) produces the exact same result as the amount (max) field. Edit: But that's not what we're looking for. We're looking for the transaction amount for the most recent transaction, not the largest ever.

Thanks Evan, I tried your formula but I get an "Invalid Expression" error. Here's how I have it set up: http://picasaweb.google.com/petzoldhaus/Netsuite/photo?authkey=siRIGWYFT9o#5189146826529409346

The issue seems to be the criteria I'm using. I guess I should have posted those. I had a transaction date range in the criteria (1/1/2005-today). When we migrated our data to Netsuite we only moved the aggregate yearly amounts (per customer). So ... when I look at the max transaction amounts pre-2005 the amounts appear…

Thanks Evan, I'll give it a try next week then.

Doh! I was afraid you were going to say that. ;)

I could, but I'm not sure that address is the only other "create" option. I suppose I could try field=none of every option there is. I'll let you know how that goes.

You are correct. I must have been blind the first time I looked for that :) So, now I just need to be able to select creation, as opposed to all the other edits I may have done to records.

I'm just going by the system notes on some of our customer records. There's type=create for record creation and for creating an address. I want to select type=create and field=record (not field=address).

Thanks Cecelia, I'll see if I can find it. If only I could search the enhancements ;) I'm not sure if I ever submitted this one.

I'd be very interested in the ability to group reports and especailly saved searches, like the folders in the file cabinet. I thought I'd started that enhancement request years ago already, but maybe not. I'd also love to see a the addition of a field in the creation of a saved search or report that lets the creator enter…

I tried that and ran into two problems. The first is that it shows all opportunities and I'm just looking to count the open ones. The count also counts an opportunity for every transaction a customer has, even ones that have no information in the opportunity field. Our sales reps are each assigned 5-15 high value custmers.…

If only we used estimates :) Ok ... it sounds like I'll need to wait for the counting issues to be fixed if I want the customers with no opportunities to display as well. Any idea what release we're looking at?

As always ... Thanks Evan. I should just add that to my signature :)

You are correct Evan. I didn't explain that very well. The purchase date range was the criteria. I neglected to mention that the results would be a count of the customer's internal id. Sorry. I was using date of first sale to eliminate new customers. Total customers for 2007 - new customers for 2007 = returning customers…

That would be terrific Evan. Thanks for the help. Let me know if you need any more info from me.

Hmmm ... I see your point. Churn rate is easily defined if you have a subscription model. For example, we have a child sponsorship program where a customer is either a sponsor or they aren't. That makes it easy to find the monthly and yearly churn rates. The rest of our donations are much more varied. Like you said,…

Thanks Evan. I'll give that a try and let you know how it goes.

Thanks for the suggestion Evan, We actually went the other route and put the cutomer field on the item since the items actually have a many-to-one relationship with the customer (ie: one customer could have many items but each item can only have one customer). Sorry about getting that wrong in my first post. Once those…

Right you are. Thanks Ray.

If you really must return yes/no or 1/0 in the results you could also use a saved search. This is only practical if you have a few items (like a dozen or so) that you are reporting on since you would need to create a formula for each item. Jim's method is much simpler and worth doing if you are ok with total products.

There's a Group with Previous Column checkbox? Where is that?

I decided to go with the hard coded dates because of the label issue. The intended audience for the results requires one click simplicity (shortcut on the dashboard). Here's a screen shot of the results tab. Note: Our customers are called supporters so the field name is different. Thanks for all your help!

Hey, Thanks. I'll give that a try on Tuesday.

You're right, I did miss that. Oops :o I will definitely post this when it's done.

Thanks Angela, That's what I would normally do but in this case there are so many formulas and groups within groups that I can't do that. Hey Netsuite! Is there any chance of getting this exposed?

If I use {TRANSACTION_LINES.TRANSACTION_LINE_ID} = 0 I get no results. Browsing the data shows that the ids seem to start at 1, not 0. Can anyone think of what I may be doing wrong? Edit: It doesn't make sense to me that the transaction total can be on line 0 and the individual line totals are on 1, 2, 3 etc. If that were…

Great, I'll try that. It would have been nice not to hard code the years but I can always stick 2008 in there as well. Then the search will be usefull for another year or so.

No takers? :) Could it be done the other way using some sort of formula that gives the sum of amount where year = 2002? .............. Individual Charity Business Foundation 2002 2003 2004 2005 2006 2007