Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mlbickett
New Contributor II

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.

1 Solution

Accepted Solutions
pablolabbe
Valued Contributor II

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.

12 Replies

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.


talk is cheap, supply exceeds demand
mlbickett
New Contributor II

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.

pablolabbe
Valued Contributor II

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

Hi Mark,

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

mlbickett
New Contributor II

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

In a sense, yes. 

mlbickett
New Contributor II

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. 

pablolabbe
Valued Contributor II

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.

mlbickett
New Contributor II

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!

mlbickett
New Contributor II

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?

pablolabbe
Valued Contributor II

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

Glad to know it works for you.

Community Browser