Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I hope someone can help me on this..
I have a rangesum expression labeled as [Net Forecast] in a pivot table where formula is
RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty))
This is my output
Material | Week | 10/19/2015 | 10/26/2015 | 11/02/2015 | 11/09/2015 | 11/16/2015 | 11/23/2015 |
AS-123 | -2000 | -3202 | -4482 | 6238 | -5042 | -6322 |
Now I want to create a new pivot table using the expression from the other pivot table where if the qty is > 1 in weekly week buckets, make it 0, if the qty is negative then convert to positive qty...
My desired output
Material | Week | 10/19/2015 | 10/26/2015 | 11/02/2015 | 11/09/2015 | 11/16/2015 | 11/23/2015 |
AS-123 | 2000 | 3202 | 4482 | 0 | 5042 | 6322 |
Hoping a response for this.
try this..
IF(
RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty)) > 1,
0,
Fabs(
RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty))
)
)
Hi! Mohammed,
Thanks you for your response but I tried it but looks different : From 10/19-11/02 - it's not doing a range sum ...11/9 is ok but in 11/16, the value from 11/09 6238 is adding the value with 11/16 5042.
Tks.
Results :
Material | Week | 10/19/2015 | 10/26/2015 | 11/02/2015 | 11/09/2015 | 11/16/2015 | 11/23/2015 |
AS-123 | 2000 | 1202 | 1280 | 0 | 11280 | 1280 |