Calculating Cumulative Revenue in Load Script

[amdbabu]

Hi,

I have a table with account id, bill date and bill amount with historical data. How do I add a new field called cumulative amount which will add amount from previous months ad current bill amount (Running Total). This needs to be grouped by account id.

Thank you

[brunobertels · Accepted solution]

Hi 

something like that 

Temp:

load

rowno() as Rowkey,

Date,

Account_Id, 

bill_amount

from your source;

Final:

load

*

if(Rowkey=1,bill_amount, if(peek('Account_Id')<> Account_Id,bill_amount, bill_amount +peek('bill_amount_cumul') ) ) as bill_amount_cumul

resident Temp order by Date,Account_Id ;

drop table Temp;

[amdbabu]

Yeah I tried this but it doesn't seem to work.  I changed the order by as account, date and it worked ! Thank you !