Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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)
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)
Thank you. It works.
What is function of NODISTINCT? If remove it, still capture the same number.
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
Got it. Thank you.