RangeSum in Script

[Anonymous]

Hello! I would appreciate if someone can help me on this...

I have an expression in pivot table  that I would like to put it in script :

= rangesum(before(total sum(if(SalesQty >0, SalesQty )),0,NoOfColumns(TOTAL)))

my table is showm below :

Rangesum:
NoConcatenate
load
Product,

Location,

Company,,
WeekDate,

alesQty,

f(Product = previous(Product]), rangesum(SalesQty, peek(Sales_Accu)),SalesQty) as Sales_Accu,

Resident demand;

But  the output doesn't  come out right.  Below is my expected out put  in yellow shade ::

Product	Location	Company	WeekDate	3/6/2017	3/13/2017	3/20/2017	3/27/2017	4/3/2017	4/10/2017	4/17/2017	4/24/2017	5/1/2017
Product  A	TX	Com A	Sales Qty	0	0	0	0	18357	0	0	0	0
Product  A	TX	Com A	Rangesum	0	0	0	0	18357	18357	18357	18357	18357
Product  A	TX	Com B	FcstQty	6000	6000	4000	6000	4000	6000	6000	4000	6000
Product  A	TX	Com B	Rangesum	6000	12000	16000	22000	26000	32000	38000	42000	48000
Product  A	TX	Com C	FcstQty	5893	1711	1624	1775	4330	1581	1628	1642	2329
Product  A	TX	Com C	Rangesum	5893	7604	9228	11003	15333	16914	18542	20184	22513

.

Pls. advise...Btw, my expression in pivot table   works perfectly.

Thank you in advance.

[aarkay29]

Try using Order by

load
Product,

Location,

Company,
WeekDate,

SalesQty,

f(Product = previous(Product]), rangesum(SalesQty, peek(Sales_Accu)),SalesQty) as Sales_Accu,

Resident demand

Order By    

     Product,

     Location,

     Company,
     WeekDate ASC;

[Anonymous]

Hi! Aar,

I tried but it only work on Com A ... 1st week 03/06 Com B and C has different value ...not sure where they come from.

[aarkay29 · Accepted solution]

This should work

if(Product = previous(Product]),

     If(Company= previous(Company), rangesum(SalesQty, peek(Sales_Accu)),SalesQty),SalesQty) as Sales_Accu,

[Anonymous]

HI!   Aar,

It still doesn't work but I added  if(Location = previous(location]) and it worked now.  Thank you so much.

Rgds