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: 
picturebox
Partner - Creator
Partner - Creator

Using Subtotal of a Dimension as Basis for %age calculation

Hello,

in charts, especially in this case Pivots, I want to calculate a %age of a dimension subtotal. In this expression

count(distinct {$ <CW ={"$(vMaxCW4)"}, SalesQuarter= , Salesmonth=, SalesWeek= >} StoreNumber)/count(distinct total {$ <CW ={"$(vMaxCW4)"}, SalesQuarter= , Salesmonth=, SalesWeek= >} StoreNumber)

I am counting the number of uniques stores for a subdimension, say Brand. I then want to find in want percentage of stores this brand is listed in. This is what the above expression does. In this case the set analysis allows me to use a particular calendar week. But what if I had as my first dimension in the table the Storetype, and I want to see what percentage my brand has not of the total count storenumber, but of the total count storenumber belonging to the storetype. In other words the total number of one storetype would be always be 100%.

I would want to take the percentage of the subtotal rather than of the grand total.

I would add a screen capture, but i don't kow how to paste that in here.

Any hints from anyone? It is probably the aggr() function, but I cannot seem to gt that to work.

Thanks

Petra

1 Reply
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

If I understand the problem you are looking to get something a bit like this, where the totals for Group1 are all different - but all shown as 100% bars:

Many thanks to NMiller who posted the solution here: http://community.qlik.com/forums/p/24590/93940.aspx#93940

Use the expression: SUM(Value) / SUM(TOTAL <Group1> Value)

The Total part is telling the expression to ignore the dimension, but the <Group1> is tying the second SUM to that dimension.

Hope that helps.

Cheers,
Steve