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.
[…] Solving the NetSuite Cumulative Saved Search Tally Challenge […]
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
[…] 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 […]
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
All the analytic functions should work including Count. See this link for a listing of Oracle PLSQL Analytic Functions.
Marty
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
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 row with the value 2 for the 2 member items:
count /*comment*/ ({memberitem.internalid}) over (partition by {internalid})This formula returns 6 rows with 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: https://blog.prolecto.com/2016/07/10/how-to-generate-netsuite-kit-item-members-weighted-price/
Marty
Hi Marty,
I’m trying to utilize this cumulative search as a search criteria (within a CASE function see below) but i keep on getting error when trying to execute, i’m trying to figure out if it should be working and I’m doing something wrong or this simply doesn’t work under as a part of the criteria
Hi
I know this post is old but it helped me get a great majority of the way to what I want to create for item transaction history so I wanted to say many thanks for that!
I am having trouble with one piece though so I am hoping someone may know the answer
When I set the criteria as shown in the related article (Posting=True, QTY=not empty, Type=Inventory item, Account:SAC= INVASSET) I don’t get transactions out like IF, CS, INV.
I’m going to keep plugging at it to see if i can figure out why but thought i would post it here just in case i have no luck with that
Thank you again!
Rick
Hello Rick,
Might this article assist? https://blog.prolecto.com/2018/02/17/learn-how-to-accumulate-netsuite-inventory-balances-via-saved-search/
Marty
Hi
Just found this post and was hoping this may solve a tricky one for us too? Any ideas whether I could use this to get around the non-summarized requirement for a dynamic group saved search? Thought Id cracked it, but much like S Feld above Im getting a NetSuite error if I try to use the formula as a standard criteria. Any ideas?
Thanks!
Matt
Hi Matt,
I have found that I need to do a fair amount of trial and error type attempts to get things working. Thus, we would have to see your specifics to comment.
Marty
What if the column that i will base the running balance from is a formula(numeric)?? what should be the expression?
James,
Do you mean a reference to another formula (number) field already defined on the search? You can’t. Each field is effectively independent.
Marty
Is it possible, within the formula itself, to add the criteria that the field must match?
e.g.
sum/* comment */({amount})
OVER(PARTITION BY {name} [where name =’inventory item A’]
ORDER BY {internalid}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Venita,
I haven’t tried to get this working yet as criteria, only as result output. As soon as I learn if we can, I will post it.
Marty
Hello Marty !
Thanks for this resource.
I have then a question. When including a case when statement and sum the result, it is not working correctly as I get multiply line. Actually in a grouped save search it is working with a max or average aggregate for sum(case when…) formula.
I have this formula for the moment but I would like to aggregate the result in one row by maximum
sum/* comment */(to_number(CASE WHEN ({transaction.type} = ‘Purchase Order’ ) then 1*nvl({transaction.quantity},0) ELSE 0 END )) OVER(PARTITION BY {name} ORDER BY {name})
Any idea if possible without grouped saved search ?
Hello Jean-Thomas,
These kinds of formulas can be difficult to diagnose. But I am curious why you are not pertaining by {transaction.type}?
Marty
Is there a way this formula can be used with an aggregate search (cumulative total on a SUM column)? I’ve tried a few variations and have been unable to get it to work (unexpected error).
Hello Chelly,
Yes, you can. See this article for inspiration:
https://blog.prolecto.com/2020/02/08/calculate-netsuite-days-sales-outstanding-dso-with-saved-search/
Marty
Thank you so much!
I am creating a SS with 52 columns in it and each column will be a text formula with 3 numbers and different highlighting criteria for each number delegated by CASE WHEN statements.
I want to incorporate this summing workaround however I need it to only sum transactions that have a date within the given week:
case when ((to_number(to_char({trandate}, ‘WW’))) <= 50) Then {quantity} else 0 end
But it seems as though when I combine the formula you provided with this formula it sums all quantities regardless. Would you be able to point me in the right direction?
As in it ignores the criteria I provide in the Case When statement and sums all transactions quantities
Hey marty,
Is there a way to get the formula to filter based on criteria provided? is there a way to get it to sum the quantities based on a Case When conditional statement?
Hello Heath,
Your questions are a good one. I don’t have a grounded answer at this point. I will need to work this a bit to confirm.
Marty
I am wondering the same thing vernita is asking! Thank you very much marty!
Hello John,
I haven’t tried to get this working yet as criteria, only as result output. As soon as I learn if we can, I will post it.
Marty
Criteria can be used as a part of the Oracle analytics function. Here is an example where I didn’t want to include bill credits in a running total for a payment file:
TRIM(TO_CHAR((SUM/* comment */
(case when {applyingtransaction.type} = ‘Bill Credit’ OR {applyingtransaction.line} =2 then 0
else {applyingtransaction.amountpaid} end)
OVER(PARTITION BY {internalid} ORDER BY TO_CHAR({internalid},’00000000000000009′) DESC )),’9999999999.00′))
Thank you Jason,
It’s good to see how we can push the tools in this manner.
Marty
This is incredible! It seriously has made our lives a lot easier. Thank you for sharing this knowledge!
Will this only work for transaction totals? I have been trying to get this formula to work when Main Line = False (to show individual line items) however the results don’t seem to be accurate
Hello Kim,
That question is too broad. But once you understand how the Oracle database works with these analytic functions, it’s up to you to be creative to get the data to output the way you want. Naturally, we can tackle your specific concerns by reaching one directly:
https://www.prolecto.com/contact-us/
Marty
Marty,
I’m trying to develop a saved search for one of my company’s KPIs. Thanks to this article I was able to get the formula to work for to generate the overall data, but when I try to aggregate it with the AVERAGE function I get an “unexpected error”. It is a transaction search. The search criteria is Type is Work Order; Status is Work Order:Closed
The formula is as follows.
CASE
WHEN
{line} = 0
THEN
CASE
WHEN
(SUM/* */({built} * ABS({quantity}) * {item.cost}) OVER(PARTITION BY {internalid}))
/
(
(SUM/* */(CASE WHEN {line} = 0 THEN {quantity} ELSE 0 END) OVER(PARTITION BY {internalid}))
*
(SUM/* */({item.cost} * {quantityshiprecv}) OVER(PARTITION BY {internalid}))
)
<= 1
THEN
1
ELSE
0
END
END
Any ideas?
Thanks,
Eric
Hi there,
Im using the formula and it’s working but I need to use a DATE filter in the saved search, and when I apply the filter for a specific day, the formula breaks and doesnt show the correct amounts.
Any idea on how to fix this?
Thanks!
Thanks for this Marty!
I’ve tried using it but it only works when my saved search results is not grouped. Is there any way to make this work when I am grouping my results already?
Hi Jennie,
You may be able to get to it. You will have to play and please do report back. These days, we are solving so many challenges with this license-free tool:
Render NetSuite SQL Queries Like Saved Searches
https://blog.prolecto.com/2021/11/27/render-netsuite-sql-queries-like-saved-searches/
Marty
how can i do the substraction
Can you explain more what you mean by subtraction?
Marty
Hi Marty,
I’m trying to show the running total of quantity on hand in the Item Transaction Sublist View. I feel like this approach should work but I’m having trouble altering the formula to work in my scenario. How would I update the formula to achieve my desired result
Hello Roscoe,
The formulas are all exposed so you have full control. We supplied an instruction sheet when we emailed you about the tool. Usually, questions relate to a pointer to the GL account(s) that are holding the inventory ledger.
If you and your organization would like to set up an ongoing support relationship, reach out to us at https://www.prolecto.com/contact-us
Marty
Thank you for this formula. I have used this successfully in many situations. I have just run into a situation where the running total randomly reset to 0 in the middle of the results. Anyone else run into this and have an explanation/solution?
GP, that usually happens when the sort on the saved search is not the same as the sort in the oracle analytics portion of the formula. Look very closely at the PARTITION BY and ORDER BY sections.
Has anyone successfully used this formula in a summary saved search? Every time I apply a summary type I get an error
Hello Kaitlyn,
Yes, a formula should work. It’s usually about data types that throw errors. The key is to know if you are grouping on it, you may need to make it a TO_CHAR() first.
Marty
Thank you for this information.
I have built some very beautify searches using NS_Concat or ListAgg based on another one of your posts.
The problem is that sometimes certain results can have a significant amount of concatenated rows, which then breaks the search.
I have gotten around this by removing them using a summary filter, but I would rather truncate the results than remove them completely.
I also tried this, but it did not work as expected. I think it’s limiting the overall results to the first 50 rows period or something. Most of my results end up blank.
LISTAGG(
CASE
WHEN ROWNUM <= 50
THEN …
I was wondering, can this workaround be used to limit the number of records returned per group to avoid hitting save search character limits?
Thanks!
Evan
Evan, thanks for bringing this forth. I haven’t tried to think about output limits in this approach and I am not surprised that there would be some limitations. I suspect the output may have a character limit.
Marty