topic Re: Create new rangesum expression from another pivot table in QlikView

Create new rangesum expression from another pivot table

Anonymous — Thu, 22 Oct 2015 00:17:15 GMT

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.

Re: Create new rangesum expression from another pivot table

aadil_madarveet — Thu, 22 Oct 2015 06:16:59 GMT

try this..

IF(

RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty)) > 1,

Fabs(

RangeSum(Alt(Before(TOTAL [Net Forecast]), 0), sum({$<Category = {'Supply'}>} Qty) - sum({$<Category = {'Demand'}>} Qty))

)

Re: Create new rangesum expression from another pivot table

Anonymous — Thu, 22 Oct 2015 15:40:55 GMT

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