Hi, all. Still only a few months into NetSuite, but getting used to things. Struggling to design a saved search, so any help would be appreciated. I need a count of the distinct customers who have spent more than a certain amount on invoices/cash sales on a certain category of products. Ultimately, this saved search is going to be used for a KPI. I know in SQL Server I'd just pull up the transaction data and filter my item to the right set then do a GROUP BY [customer] HAVING sum(sales) > 500... or something similar. Having trouble figuring out how to translate that into a saved search. Any suggestions?

you should be able to create a transaction search and in criteria you would put type of transacations you want and certain category, so i am guessing you have field on the item record that distinguish those items, so you would filter based on those criteria

Yes, I can get that part working. But how do I collapse that down to 1 number counting the distinct number of customers? If I group on customer field, I'll get a list of just the distinct customers, but I'd like to get a count of them for a KPI.

instead of group try count

Ah, that does work. I assumed it would count all the transaction rows, but it does count distinct records. Thanks

So here's the trade-off, you can use a

Summary Criteria

for 'SUM of amount gt 500', and then you'll only get groups that meet that summary criteria (i.e. if grouped by customers, you'll only get customers who've spend that in total) OR You can change the results to COUNT, which will count the number of distinct customers, but, the

Summary Criteria

(for 'SUM of amount gt 500') will apply to the whole group. Meaning you'll get a count of distinct customers, sure, but it will include all customers, as it's summing all of their spends

Ah, that's not great.

Using the former solution, you'll still get a distinct count (in the top-right corner of the search). However it's not KPI-able (not using the KPI meters at least)

Coming from SQL Server, I wish dearly they'd implement a way to use another saved search as criteria for a search -- eg case when X in (select Y from [otherSavedSearch]) then 1 else 0 end

You might be able to do the case when and reference another saved search , I am not that advanced with SQL

well, there is SuiteQL (not accessible via UI) and SuiteAnalytics Workbooks. SA workbooks can do multiple/left-hand joins (as opposed to Saved Searches). You can also make pivot charts from them (which might get you the right customers). But I don't think it'll solve your KPI reqm.

Thank, @David B -- I just started trying to build out a SuiteAnalytics Workbook for my requirement. Doesn't NEED to be a KPI, but should be easily displayed on dashboard. That's my real requirement.