Running Sum For Each Month at Scirpt level.

[s4ni9r]

Hi Everyone,

I have the following table, need to calculate runningsum for sales_amount and price_amount for each month

totalQuantity:
Year,
Month
Market,
Country,
Dist
Mode,
title,
Scope,
Segment,
price_amount,
Sales_amount,
Metric
FROM table1;

[OmarBenSalem]

In the script?

left join
LOAD

    Year,

    Month,

     rangesum(peek(ru_price_amount),price_amount) as ru_price_amount,
     rangesum(peek(ru_price_amount),Sales_amount) as ru_Sales_amount

RESIDENT totalQuantity;

[s4ni9r]

Hi Omar Sir,

The Running Sum should Reset for every year, Could you please help me with this.

Thanks 

[OmarBenSalem]

try this 

left join
LOAD

Year,

Month,

   if(Year=previous(Year),

rangesum(peek(ru_price_amount),price_amount),price_amount) as ru_price_amount,
 

if(Year=previous(Year),

  rangesum(peek(ru_price_amount),Sales_amount),Sales_amount) as ru_Sales_amount

RESIDENT totalQuantity;

[s4ni9r · Accepted solution]

Thank You Very Much Sir,

How can we acheive this in UI level? Could you please help me 

Thank you very much again sir

[OmarBenSalem]

aggr( rangesum(above( sum(price_amount),0,RowNo())),Year,Month)

aggr( rangesum(above( sum(Sales_amount),0,RowNo())),Year,Month)

[s4ni9r]

Thank You Very Much for your help  Omar Sir

[OmarBenSalem]

My pleasure !

[s4ni9r]

Hi Sir,

I have got a different requirement , Could you please help me .

i have got the below table

Month	Value
Jan	A
Jan	B
Jan	C
Jan	D
Feb	A
Feb	B
Feb	C
Mar	A
Mar	B
Mar	C
Mar	D
Mar	E

Expected Result :

if the User Selects, JAN - The distinct count of values in Jan- 4 

if the User Selects, JAN +FEB - The distinct count of values in JAN+FEB -  4 

if the User Selects, JAN +FEB+MARCH - The distinct count of values in JAN+FEB+MARCH - 5(A,B,C,D,E)

expected result
jan	4
Feb	4
Mar	5