topic Percent to Subtotals in QlikView

Percent to Subtotals

Wed, 26 May 2010 18:59:48 GMT

Hello -

I am relatively new to QlikView and have been creating various reports. Many of my reports contain % to totals and % to subtotals.

Example:

Style	Description	Units	% To Total
ABC1	Full Coverage Bra	75	35.71%
ABC2	Full Coverage Bra	55	26.19%
ABC3	Full Coverage Bra	80	38.10%
	SUBTOTAL	210	61.76%
ABC4	Demi Bra	95	73.08%
ABC5	Demi Bra	35	26.92%
	SUBTOTAL	130	38.24%
	Total Bra	340

How do I replicate all above percentages in QlikView 9.0?

Percent to Subtotals

boorgura — Wed, 26 May 2010 19:12:41 GMT

In the expressions TAB - there is option to CHECK relative. That will make the values relative to the total and show it as a %.

Percent to Subtotals

Wed, 26 May 2010 19:27:10 GMT

Thanks Rocky. This example is a pivot table, with subtotals, and the relative box isn't available for pivot tables. If I do as a straight table, and check relative, then I get just % to the Grand total and not % to Subtotal results, which is what I'm looking for. Any other suggestions?

Percent to Subtotals

Wed, 26 May 2010 19:54:09 GMT

You may be able to use dimensionality. Dimensionality is designed to return the aggregation level of an expression total. In the example above, Dimensionality() in its own expression would return 1 on the row for Total Bra, 2 on the subtotal rows for each bra type, and 3 for the lowest level bra type/style data. That said, you could write your expression to run different expressions based on their aggregation level like this (using pick to avoid nesting ifs):

pick(dimensionality(), null(), Sum(Units)/Sum(TOTAL Units), Sum(Units)/Sum(TOTAL <Description> Units))

In this case, I used pick to select the appropriate formula from the ordered list using the returned aggregation level from dimensionality.

Percent to Subtotals

Wed, 26 May 2010 20:50:47 GMT

This did the trick!!! Thank you 🙂

Percent to Subtotals

Wed, 06 Oct 2010 13:41:43 GMT

Annel, what formula did you get?

I'm trying Aaron formula, but isn't work....

Thanks

Percent to Subtotals

Wed, 06 Oct 2010 18:01:12 GMT

OK! IT WORKS!!

Thanks Aaron 😄

Percent to Subtotals

Wed, 05 Jan 2011 10:15:16 GMT

Hi!

Is it possible to consider a dimension group? Instead of using a particular dimension (like, for example, "Units").

A group like --> Dimension Group (containing "Units", "Sales", "Salesman",...)

And each dimension of the group can apply the same calculation for the subtotals....

Thanks a lot!

Best Regards,

Adri

Percent to Subtotals

Wed, 05 Jan 2011 11:39:28 GMT

Ok, I solve it!

Instead of consider the single dimension like:

pick(dimensionality(), null(), Sum(Units)/Sum(TOTAL Units), Sum(Units)/Sum(TOTAL <Description> Units))

Consider the dimensions group as follows:

pick(dimensionality(), null(), Sum(Units)/Sum(TOTAL Units), Sum(Units)/Sum(TOTAL <[Dim Group]> Units))

It works!!