Hi all - trying to get a formula that calculates the quantity sold of a particular item on a sales orders over the last 12 months in one single saved search. I'm using the following formula but i must be missing something because all of the values end up the same quantities across the board when I preview - all result sums seem to be all sales within the past month, so I'm sure it's something easy I'm just missing. Any ideas?

CASE WHEN {trandate} BETWEEN to_date(TRUNC(TRUNC({today}, 'MONTH')-12,'MONTH'), 'MM/DD/YYYY') AND to_date(LAST_DAY(TRUNC({today}, 'MONTH')-11)) THEN {quantity} ELSE 0 END

CASE WHEN statement above, modified slightly for each month (see screenshot).

I'm grimacing a bit reading that setup. Trying to think of an easier way to do it.

there's a suiteanswers for this

I don't know that decode is necessary here.

oh use maximum not sum

I know. If you can think of a better way altogether, I'm all ears. I'm used to writing scripts directly in SQL SSMS and am fairly new to doing this on the front-end (I'm pretty new to NetSuite overall).

@KevinJ of Kansas thank you! The way I was searching for results wasn't giving me the results so I appreciate you sharing the link!

I find searching suite answers is more of an art than a science.

@KevinJ of Kansas thanks for the tip! Yeah it's taking me a while to get used to. I kept getting results for calculating days between two dates instead of this 🙂

if that hadn't found it, i'd have tried

column formula month