topic Re: Pivot table with calculate dimension in QlikView

Pivot table with calculate dimension

YJ_SG — Sat, 16 Nov 2024 18:34:32 GMT

I am going to do a waterfall table to show every week Qty and incoming Qty of next 8 weeks. (see attached Output Table)

I am able to show waterfall in Pivot table, but not able to calculate Qty through SUM(Qty) in dimension. Please help.

Order:
LOAD * INLINE [
Product,Week,Qty,Incoming,Incoming Week
A,wk01,100,100,Wk02
A,wk01,200,100,Wk03
A,wk01,0,100,Wk04
A,wk01,0,100,Wk05
A,wk01,0,100,Wk06
A,wk01,0,100,Wk07
A,wk01,0,100,Wk08
B,wk01,200,200,Wk02
B,wk01,300,200,Wk03
B,wk01,0,200,Wk04
B,wk01,0,200,Wk05
B,wk01,0,200,Wk06
B,wk01,0,200,Wk07
B,wk01,0,200,Wk08
A,wk02,150,150,Wk03
A,wk02,250,150,Wk04
A,wk02,0,150,Wk05
A,wk02,0,150,Wk06
A,wk02,0,150,Wk07
A,wk02,0,150,Wk08
A,wk02,0,150,Wk09
B,wk02,250,250,Wk03
B,wk02,350,250,Wk04
B,wk02,0,250,Wk05
B,wk02,0,250,Wk06
B,wk02,0,250,Wk07
B,wk02,0,250,Wk06
B,wk02,0,250,Wk09
];

Re: Pivot table with calculate dimension

zhadrakas — Fri, 29 May 2020 05:58:31 GMT

if you want to use an aggregation on dimension level you will need to use the aggr() function.

Somthing like this.

=aggr(NODISTINCT sum(Qty) , Week, Product)

Re: Pivot table with calculate dimension

YJ_SG — Fri, 29 May 2020 08:36:19 GMT

Thank you. It works.

What is function of NODISTINCT? If remove it, still capture the same number.

Re: Pivot table with calculate dimension

zhadrakas — Fri, 29 May 2020 08:39:49 GMT

in default aggr will use its aggregations inside in distinct mode.

Means if you sum() some data you might get a wrong result like

Data

sum(aggr(sum(data))) = 6

sum(aggr(NODISTINCT sum(data))) = 7

Re: Pivot table with calculate dimension

YJ_SG — Fri, 29 May 2020 08:50:28 GMT

Got it. Thank you.