CODEWITHSUNDEEP
netsuite
Nir Levi
Nir Levi

Reputation: 1

Formula in saved search

I have created a saved search that in the criteria I have all the bank accounts with multiple currencies, in the results I have a column that calculate the amount (account currency) using this formula:

CASE WHEN {account.number} IN ('10003','10005','10007','10008','10009','10010','10011','10012','10013','10101','10102','10103','10104','10105','10106') THEN {amount} ELSE {fxamount} END

I need to add another column that calculate the accumulated balance for each day , according to the account currency, the same way it appears in the trail balance netsuite report.

for example if the first transaction on 01/01/2021 in BANK USD is 100 so I want the value in the new column will be 100, if on 02/01/2021 I have payment of -45 USD then I want the value will be 55 (100-45) etc...

I hope its clear enough, please advise !

Nir

Upvotes: 0

Views: 1054

Answers (1)

Cody Schram
Cody Schram

Reputation: 51

If I understand your question, you want a running total?

Perhaps this will work for you, in a currency formula use:

SUM/* comment */(CASE WHEN {account.number} IN ('10003','10005','10007','10008','10009','10010','10011','10012','10013','10101','10102','10103','10104','10105','10106') THEN {amount} ELSE {fxamount} END) OVER(PARTITION BY {account.number} ORDER BY {internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

I found this solution from this blog:

https://blog.prolecto.com/2015/05/26/solving-the-netsuite-cumulative-saved-search-tally-challenge/

I apologize if I misunderstood the question.

Upvotes: 0

Related Questions