14 Replies Latest reply: Dec 16, 2015 6:20 PM by Mark Bickett

# Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

I am working in Qlik Sense, I have a ratio that I want to calculate comparing a line item measure to the subtotal of a different measure for the dimension.

Dimension A - Value 1

Measure 1     Measure 2     Desired Result

Item 1               10               20                    10/100 = 10%

Item 2               20               30                    20/100 = 20%

Item 3               10               50                    10/100 = 10%

40              100                    40/100 = 40%

Dimension A - Value 2

Measure 1     Measure 2     Desired Result

Item 1               15               20                    15/100 = 15%

Item 2               30               30                    30/100 = 30%

Item 3                 5               50                      5/100 = 5%

50              100                    50/100 = 50%

This is the only way I have found to have the divisor be the sum of Measure 2 across the Dimension Value

SUM ( Measure 1 )  / SUM(TOTAL <Dimension A>  Measure 2 )

Unfortunately, this requires me to declare an explicit value for Dimension A, is there a way to select the Dimension dynamically based on the Dimension the chart (Object) the expression is used in?  Otherwise I fear I will have to declare it for each Dimension I want to use it in.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

If you're only using one dimension then SUM ( Measure 1 )  / SUM(TOTAL Measure 2 ) should work too.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

Good point, it does work with one Dimension in the Chart, but I am using a second dimension and creating a stacked bar, so I would be looking for a way to dynamically use the first dimension in the chart the measure is used in.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

Hi Mark,

Are you trying to mimic the cycle dimension feature from Qlikview ?

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

In a sense, yes.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

My goal is to not have to manually match the Dimension name in my chart to the Dimension clause of the TOTAL statement, which cycling would mimic.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

If you have a variable to store the selected field used as dimension you can use:

SUM (TOTAL <\$(vDimensionSelected)> Measure2)

vDimensionSelected contains the field used as Dimension.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

True, then that would perform like cycling.  I was planning to have multiple similar charts displaying so thought I may still have to manage more objects with a variable for each chart, but the number of charts I want to display compared to the number of dimensions I want to be able to run it by is much less, so makes sense.  Thanks!

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

The variable works great in the expression, but having trouble getting the variable to be recognized as an actual dimension in the chart.  I tried using an expression referencing the variable as well as creating a dimension object referencing the variable, but it just sees it as a unique text item with 1 value.  Thoughts?  Is there some additional syntax I need to use to pass it to the chart?

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

I use this syntax :  ="\$(=vDimension)"

Remember ! You need to start the expression with an equal sign.

• ###### Re: Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

I figured it out, needed to use '\$(vName)' instead of just 'vName'