Pivot table with calculate dimension

[YJ_SG]

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
];

[zhadrakas · Accepted solution]

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)

[YJ_SG]

Thank you. It works.

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

[zhadrakas]

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

1

2

3

1

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

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

[YJ_SG]

Got it. Thank you.