Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Highlighted
Partner
Partner

Calculated Difference between measures at chart level

Hello

We have a report where there is a need to add a measure that will always calculate difference between the columns before it.  Similar to below table

 ActualBudgetVar
Measure11046
Measure2550
Measure3642

 

This could be done using Column(1) - Column(2) in theory.

However if you notice here i have a Dimension that has {'Actual', 'Budget'} and i have three separate Measures, so Column(1) - Column(2) will only give me difference between the measures for the same value of the Dimension.

 

Is there a way to do this in Sense ?

 

Labels (4)
3 Replies
Partner
Partner

Re: Calculated Difference between measures at chart level

From my understanding this type of behavior was present in QlikView ?
Where you could use the Column label as a reference and use it in your expressions
kaanerisen
Contributor III

Re: Calculated Difference between measures at chart level

Hi,

You can use valuelist to create custom columns.

Dimension:

=ValueList('Actaul','Budget','Var')

 

Measure:

Pick(Match(ValueList('Actual','Budget','Var'),'Actual','Budget','Var'),
Sum({<TYPE={'Actual'}>}VALUE),
Sum({<TYPE={'Budget'}>}VALUE),
Sum({<TYPE={'Budget'}>}VALUE)-Sum({<TYPE={'Actual'}>}VALUE)
)

 

Untitled.png

Hope it helps.

Partner
Partner

Re: Calculated Difference between measures at chart level

hi thanks for the post

This helps to a certain point but still facing some issues:

  • If i do not put a second measure the row stays blank for some reason:

image.pngwith only one measure defined

 

image.pngWith Two Measures shows correctly

  • With this approach i will need to create a new Var measure for each measure already in my model
    • As in if i have Sales & Cost & Revenue & HeadCount as measures, i will need to have new [Sales with Var], [Cost with Var], [Revenue with Var], [Head Count with Var]
    • This will make it harder for self-service or maintenance in the long term

Ideally, if there is a way to do as was present in QLikView, where you can just reference the Column named Actual & Budget and get difference between them.