topic Re: What is wrong with my rangesum? in QlikView

What is wrong with my rangesum?

Anonymous — Wed, 04 Nov 2015 23:26:18 GMT

Hello!

Maybe someone could help me on this...

I have a pivot table as shown below

Now I want to create a cumulative formula where Supply - Demand by using the formula but the rangesum didn't work...it's just Supply-Demand...

=RangeSum(sum({$<key = {'Supply'}>} Qty) - sum({$<Key = {'Demand'}>} Qty))

Results of my 2nd pivot table

Plant	Material	MonWeek	11/02/2015	11/16/2015	11/23/2015	12/14/2015	12/21/2015	12/28/2015	01/18/2016
HK	A		4000	8000	-12000	34000	-20000	-14000	-6000

I also tried using formula below but still did not work

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

My output should be like this...

Expected Output...

Plant	Material	11/02/2015	11/16/2015	11/23/2015	12/14/2015	12/21/2015
HK	A	4,000	12,000	0	34,000	14000

Rgds

swuehl — Wed, 04 Nov 2015 23:35:43 GMT

Try

RangeSum(Before( sum({$<Key = {'Supply'}>} Qty) - sum({$<key = {'Demand'}>} Qty),0,ColumnNo()) )

Anonymous — Wed, 04 Nov 2015 23:46:09 GMT

hi! Swuehl,

Thank you for your quick response ...I tried the formula but this is the result ...

11/16 should be 12K...

Tks.

Plant	Material	Week	11/02/2015	11/09/2015	11/16/2015	11/23/2015	11/30/2015	12/07/2015	12/14/2015
HK	A		4000	0	8000	-12000	0	0	34000

swuehl — Wed, 04 Nov 2015 23:58:50 GMT

Seems to work for me.

Please have a look at the attached sample file (or post a sample file with your data).