This article is relevant if you are seeking to produce a cumulative (or running) total via NetSuite Saved Search technology.

## Background

Nearly 3 year ago, I wrote an article, NetSuite Saved Search Running Totals Sublist Challenge, where I was challenged to solve a Saved Search problem to aggregate and tally data. Â Today, that challenge is solved due to the kindness of Â Robert Pottorff, a NetSuite developer serving Lone Star Percussion.

Robert reached out to share with me that he was able to trick the NetSuite Saved Search parser by allowing it use a “SUM()” in a non-aggregate search. NetSuite assumes that if you use “SUM(” in a formula, you are trying to aggregate. We really want to use Oracle’s Analytic Function set. By placing a SQL comment in the formula field, NetSuite does not assume you are doing an aggregate and passes the string to the underlying Oracle database to do the work we need.

I went ahead and produced a list of transactions to see if I could get it to perform — and indeed it did!

## Sample Running Total Saved Search

Here is the actual formula text I used to build the cumulative total based on the amount field:

sum/* comment */({amount}) OVER(PARTITION BY {name} ORDER BY {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

See images for the saved search definition and the results.

## Solving NetSuite Technical Challenges

When Robert helped me solved this, I jumped for joy. Â Robert would be a welcome addition to our team of consultants — it’s this kind of innovation that we, and our clients, appreciate. Â If you would like help with your Saved Searches or other NetSuite enhancements, Â let’s have a conversation.

## 34 Comments

This is amazing, many thanks to you and Robert.

Hi Marty,

Thanks for sharing this. I did try doing this in a saved search. I replaced the amount in your numeric formula with the internal Id of the amount field I wanted to have a running total for.

Unfortunately, it did not work. The numeric formula only returned the exact same amount field, without any cumulative totals. Can you pls. let me know why it is not working & how to fix this in the saved search? Truly appreciate it!

Thanks a lot!

Dolly.

Hi Marty,

I tweaked the formula & retried it. It now works & gives the desired results! Pretty awesome!

Thanks a lot!

This is really great and many thanks!

Some may find this useful:

I’m working on a transaction search for ‘Available to Promise’. I need a tallied sum that partitions over ‘item’ and then ‘location’. By trial and error I found that simply separating the fields by a comma does the trick here. Here’s my syntax:

sum/* comment */(NVL({item.locationquantityavailable},0)+NVL({quantity},0)-NVL({quantityshiprecv},0)) OVER(PARTITION BY {item},{item.inventorylocation} ORDER BY {custcol_eta},{internalid},{linesequencenumber} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Notice that I also used commas for the order by to get the tallied sum ordered accordingly.

Thank you Matthew. Excellent, we need all the examples we can get of this powerful capacity!

Marty,

Thanks for your original post and the update. We are trying to solve a running total issue for inventory items which are impacted with issues (WO) and receipts (POs) where the quantity of the transaction is always pos. but we want issues to be subracted from the total and receipts to be added. Can you think of a way to use Robert’s solution to achieve a running total?

Hi Jim,

Couldn’t you create a conditional column based on transaction type to get quantity elements positive and negative and then use the technique against that?

Marty

Thank you , this is so helpful!

I am very new to NetSuite and using formulas in saved searches. I am finding your site to be invaluable.

how would I tweak this formula if I just need the final total , and not a running total?

Hi Jane,

Did you try to use the “Show Totals” under the Results Tab?

Marty

oh, I just realized it was that I had a typo on my formula when i was putting it in so yes..this was all I needed for my second column. Perfect..thanks!

sum({amount})OVER(PARTITION BY {name})

I have attempted this for the last 3 days with no luck. Looking to created a running total for inventory items to monitor what transactions are creating negative on hand balances, no luck.

Hi Dan,

Are you able to get any running totals working? Can you share more details to see if we can work through it?

Marty

Hi Dolly/Marty,

I have the same problem where my formula matches the amount field, without any cumulative totals. Any idea what I’m doing wrong?

Hi Marty,

I am facing a problem where in in one column i have count of all the transaction and in other the count of transaction with specific status. I now want a third with the percentage of above 2. Is it possible in Netsuite using saved search?

Hi Mitisha,

I don’t believe it can be done with native saved search as you need to get the grand total to then later divide the results. Naturally, this can be done if you take the saved search into SuiteScript.

Marty

Hi Marty,

Thanks for this! I’ve used this in a couple of saved searches successfully, but now I’m trying to do the same with a summarized saved search. I’d like to get transaction YTD amounts, grouped by customer. I’ve tried different summary types, modifying the clauses, etc. I either get an Unexpected Error in saving or I get an invalid expression in the column. Any ideas?

Also for Mitisha’s question, one might try to create a summarized saved search with a formula (Numeric), summary type MIN, and a formula like this:

sum({amount})/count({internalid})

You can probably modify the count to include a case statement as well.

Hi George,

You seem to be on the right track. Those error messages are common until you dial it in. I have no offer: it is trial and error.

Marty

I’d be interested to see if you can get this to work on a summarised saved search, too. I’ve been playing around for a while, but in SQL you’d usually use a CTE or similar to group the data before the partition.

I’m trying to make an Empty Bin Report, I can’t figure out how to sum all items on a Bin(could be different items) so if the SUM is = 0 mean that the Bin is empty and only show me that bins.

Any help?

Hello Alieno,

Are you familiar with the Criteria and the Summary tab? That tab is similar to a SQL Having Clause which allows you to produce an Aggregate Count and then set specific criteria to see if the result set would qualify.

Marty

It would be great if you could use the same approach using the analytic functions to perform a calculation after having grouped.

I tried to do it by using a function the way it is described here, and it works. The problem is that then I want to group the result set, and it won’t let you. You can’t have the function as a group by.

It’d be great if somebody knew how to do this

Hi marty,

I tried your formula for cummulative totals

but it is showing error.

SUM({custrecord_hm_bc_payroll_net_pay}) OVER(PARTITION BY {custrecord_hm_bc_payroll_batch})

Summary type : SUM

Formula(numeric)

Can you please check, is that correct…?

Thanks in advance.

Hello Ashbaq,

The best I can offer is that it is trial and error and we all go through it. Be sure to really learn how Oracle works on these Analytical functions. Here is an article that may help you gain more insight.

Hi Marty

Slightly tenuous link to above but I’ve spent some time on this to no avail and I thought you might be able to help.

I need to use the count() function in a formula. Iâ€™m trying to return the 2nd last sale transaction for each customer in a saved search. The logic Iâ€™m using is that if I use the rank formula to rank each transaction by date then add 1 to that and divide the result by the count of the transaction and that is equal to 1 then Iâ€™ve got the 2nd last transaction.

My first step was to just use the count() function in any formula (e.g. COUNT({transaction.number}))

but anytime I use count() in a formula NetSuite doesnâ€™t even return the column in the results

Any ideas?

Thanks

Stephen

The reason I’m asking on this thread is because you’ve used sum() in a formula above. I cant seem to use any of the summary type functions in a formula

thanks

Stephen

Stephen,

These analytic formulas can not use the native Summary features which effectively are Equivalent to SQL Group By Operators.

Marty

All the analytic functions should work including Count. See this link for a listing of Oracle PLSQL Analytic Functions.

Marty

Thanks for the info on this formula. However, it appears that when a product has more than one page it doesn’t calculate all the pages or at least doesn’t match our actual on hand numbers. I’m sure it’s something I’m doing or not doing. Would you be able to shed some light on it for us?

Hi Marshall,

I have found that you may need to think carefully about the order the transactions are in for your formula to be reliable. It’s hard for me to assess in general. But I have definitely been able to get the next page to get the right results once I gave careful consideration to the Partition By clause.

Marty

Hi Marty,

I’ve been looking for a way to do the 80/20 principle for god knows how long and I stumbled across your link from the NS forums. So I used your formula above as per your first screenshot (hoping to get something like that, total based on Amount)

sum/* comment */({transaction.amount})

OVER(PARTITION BY {name}

ORDER BY {internalid}

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

and this is just giving me the ‘amount’ from my ‘2017 Amount Sales’.

I’ve been trying to convert this excel formula to your formula so that I can achieve the results I need. I’ve been trying for nearly 2 days and getting no-where.

Excel Formula – =SUM($E$2:E3)/$E$10 (E2 being the 1st items sales amount and then going down the list E2 being the 2nd items sales amount) then dividing it by E10 (Total amount of sales).

I’ve tried to do this –

sum/* comment */({transaction.amount})/CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=’2017′ THEN {transaction.amount} ELSE NULL END

OVER(PARTITION BY {name}

ORDER BY {internalid}

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

and tried this too

sum/* comment */({transaction.amount})+({transaction.amount})/CASE WHEN TO_CHAR({transaction.trandate},’YYYY’)=’2017′ THEN {transaction.amount} ELSE NULL END

OVER(PARTITION BY {name}

ORDER BY {internalid}

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Either I get invalid expression or getting weird results like the same figure or ‘1’.

is there any way you can help me, please? I’ve almost pulled out most of my hair.

Thank you

Hi Mayur,

I believe you need additional handling for your nulls. You can test further if the result set returns a Null and if so, then possibly return a zero. See how this will return a Zero: ISNULL((45 / NULLIF( 0, 0 )),0)

Marty

2018 and still relevant. Thank you guys

I realize this is a really old post but seems like it’s still getting comments so…

Any idea why this seems to work on some record/field combinations but not others? Here’s a test setup. I’m searching an item record for a specific kit. The kit has 2 member items and 6 transaction records.

This formula returns

1 rowwith the value 2 for the 2 member items:`count /*comment*/ ({memberitem.internalid}) over (partition by {internalid})`

This formula returns

6 rowswith the value 6 for the 6 transactions:`count /*comment*/ ({transaction.internalid}) over (partition by {internalid})`

So I get the expected results count when using member item fields but I get duplicate rows when using transaction fields?

Hi Don,

Most likely, you are reaching out via a join to a transaction and you need to set the join condition to be mainline = true.

Also, see this article on how to search kits: http://blog.prolecto.com/2016/07/10/how-to-generate-netsuite-kit-item-members-weighted-price/

Marty

## 2 Trackbacks

[…] Solving the NetSuite Cumulative Saved Search Tally Challenge […]

[…] additional analysis can be performed. Â The real opening to this solution is in my article, “Solving the NetSuite Cumulative Saved Search Tally Challenge‘. Â Here, the trick is to learn how to drive an Oracle based aggregate search into a standard […]