Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This should work
if(Product = previous(Product]),
If(Company= previous(Company), rangesum(SalesQty, peek(Sales_Accu)),SalesQty),SalesQty) as Sales_Accu,
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;
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.
This should work
if(Product = previous(Product]),
If(Company= previous(Company), rangesum(SalesQty, peek(Sales_Accu)),SalesQty),SalesQty) as Sales_Accu,
HI! Aar,
It still doesn't work but I added if(Location = previous(location]) and it worked now. Thank you so much.
Rgds