Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
Showing results for 
Search instead for 
Did you mean: 

Using Subtotal of a Dimension as Basis for %age calculation


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.



1 Reply
MVP & Luminary
MVP & Luminary

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.