Qlik Sense - Referencing Dimension Subtotals Dynamically in Line Chart

[Anonymous]

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.

[Gysbert_Wassenaar]

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

[Anonymous]

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.

[pablolabbe]

Hi Mark,

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

[Anonymous]

In a sense, yes. 

[Anonymous]

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. 

[pablolabbe · Accepted solution]

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.

[Anonymous]

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!

[Anonymous]

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?

[pablolabbe]

Glad to know it works for you.