topic Re: RangeSum set analysis in Pivot Table in QlikView

RangeSum set analysis in Pivot Table

Simon_BI — Tue, 31 Dec 2019 01:07:02 GMT

Hi ,

I have a problem here regarding RangeSum set analysis in Pivot Table.

Below table is the output i want and the input is the first 4 columns. The final column is the accumalation of the STO needs on product level.

I want to use set analysis to achieve this output. So i used RangeSum function.

When i only add "Product "and "STO _NO" dimensions in the pivot table, the analysis i wrote "rangeSum(Sum(STO_Needs),Above(Accumalation))" works as i expected. But when i added the third dimension "DC_NO", this set analysis didn't work.

Could someone help here?

Attached is the QVW file you can work on.

Product	STO_No	DC_No	STO Needs	Accumalation
00169560	001	DCGACN	8	8
00169560	002	DCGACN	8	16
00169560	003	DCGACN	8	24
00169560	004	DCGACN	8	32
00169560	005	DCGACN	8	40
00169560	006	DCGACN	8	48
00169560	007	DCGACN	16	64
00169560	008	DCGACN	8	72
00169560	009	DCGACN	8	80
00169560	010	DCGACN	8	88
00169560	011	DCGACN	8	96
00169560	012	DCGACN	8	104
00169560	013	DCGACN	8	112
00169560	014	DCGACN	8	120
00169560	015	DCGACN	16	136
00169560	016	DCGACN	16	152
00169560	017	DCGACN	8	160
00169560	018	DCGACN	8	168
00169560	019	DCGACN	8	176
00169560	020	DCGACN	8	184
00169560	021	DCGACN	16	200
00169560	022	DCGACN	16	216
00169560	023	DCGACN	8	224
00169560	024	DCGACN	8	232
00169560	025	DCGACN	8	240
00169560	026	DCGACN	8	248
00169560	027	DCGACN	16	264
00169560	028	DCGACN	24	288
30234229	001	DCGAPAU	10	10
30234229	002	DCGAPAU	10	20
30234229	003	DCGAPAU	10	30
30234229	004	DCGAPAU	10	40
30234229	005	DCGAPAU	10	50
30234229	006	DCGAPAU	10	60
30234229	007	DCGAPAU	10	70
30234229	008	DCGAPAU	10	80
30234229	009	DCGAPAU	10	90
30234229	010	DCGAPAU	10	100
60185269	027	DCGACN	16	16
60185269	028	DCGACN	24	40

Re: RangeSum set analysis in Pivot Table

tresB — Tue, 31 Dec 2019 06:31:29 GMT

Try like:

RangeSum([STO Needs], If(Product=Above(Total Product), Above(total Accumalation),0))

Re: RangeSum set analysis in Pivot Table

Simon_BI — Tue, 31 Dec 2019 06:41:19 GMT

Hi Treseco,

Really thanks for your help.

It works and also give me the inspiration. Super thanks.

Re: RangeSum set analysis in Pivot Table

alex00321 — Tue, 31 Dec 2019 06:59:45 GMT

Hi Nice solution, but can you explain why this will work? Thanks!

Re: RangeSum set analysis in Pivot Table

tresB — Tue, 31 Dec 2019 07:19:40 GMT

Hi Alex,

Your question sounds(to me) in two possible ways:

- You think that this might not work (if so, please let me know why. I would give another try to fix that)

- You might not have understood some of the section of the expression (if so, please let me know exactly which section you need clarification with).

Thanks.

Re: RangeSum set analysis in Pivot Table

alex00321 — Tue, 31 Dec 2019 07:37:15 GMT

Hi thanks for the reply, it's the latter, the expression you post contains this: If(Product=Above(Total Product)

which was added to let the formula work, I don't understand why we need to add this part to let it work. Thanks!

Re: RangeSum set analysis in Pivot Table

tresB — Tue, 31 Dec 2019 07:46:50 GMT

Without If(Product=Above(Total Product),you would notice that the accumulation would happen across Product dimension, i.e. - irrespective of product the accumulation goes adding up start to end. However, with this code we are checking if there is a change in product value in dimension. If there is a new product then we are resetting the accumulation with zero reference to the earlier value.

Hope this helps.

Re: RangeSum set analysis in Pivot Table

alex00321 — Tue, 31 Dec 2019 07:56:21 GMT

Thanks, you are awesome!! Happy New Year!