Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table where I have an expression market share which is calculated by the following expression
Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)
/
Sum( total{1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)
Here the problem is I want the denominator value as the partial sum of that particular dimension value but because I have taken
total qualifier it is taking the sum of all dimension values.
for eg , for commodity ABC,Supplier XXP the spend is 82,916, the market share should be 53.177(82916.90/155924.04)
but I am getting 2.44 because I have used total qualifier it is taking the whole commodities spend.
How to get that partial sum of commodity in the expression.
The expression should work...
Could you please provide us a sample qvw?
Thank you.
Marc.
I changed it but the same result as above even this time the partial sums changed to 100%
kiran
Finally got it , when we have calculated dimension we have to use advanced aggregation using aggr().
The following post helped me alot
The expression which I have used for market share is
Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend)
/
Aggr(NODISTINCT Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter)}>}Spend),Commodity)
Thanks
Kiran Kumar .CH