Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kkkumar82
Specialist III
Specialist III

Want to calculate market share based on the partial sums(second dimension percentages)

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.

12 Replies
Anonymous
Not applicable

The expression should work...

Could you please provide us a sample qvw?

Thank you.

Marc.

kkkumar82
Specialist III
Specialist III
Author

I changed it but the same result as above even this time the partial sums changed to 100%

kiran

kkkumar82
Specialist III
Specialist III
Author

Finally got it , when we have calculated dimension we have to use advanced aggregation using aggr().

The following post helped me alot

Quick Development Tips: Qlikview: Aggregation 1: How to calculate percentages at second dimension le...

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