1 Reply Latest reply: May 17, 2010 7:56 PM by Steve Dark

# 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

• ###### Using Subtotal of a Dimension as Basis for %age calculation

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