Create new rangesum expression from another pivot table

[Anonymous]

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.

[aadil_madarveet]

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))

     )

)

[Anonymous]

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