NetSuite Saved Search That Counts Invoices In An Amount Range

Question

I'm trying to create a saved search that groups invoices by amount in buckets of $1,000. Then I would like to count the number of invoices in that range.

What I have tried is creating a grouped text formula with the formula TO_CHAR(ROUND({amount}, -4)) and then another column that is the internal id with the Count summary type. This would work but NetSuite says the formula text column cannot be grouped, presumably because it uses the amount field in the formula.

Does anyone know of an alternate way that I can accomplish this?

Answer 1

If I understand well your needs, The current formula, group all the transaction from 0 to 4999 together, ... I have updated it to group all the transaction from 0 to 999, then 1000 to 1999 and so on:

a text formula with the following:

(count/* comment */(ROUND(TRUNC({amount},-3),-3)) OVER(PARTITION BY (ROUND(TRUNC({amount},-3),-3)) ORDER BY (ROUND(TRUNC({amount},-3),-3)))