Qlik Community

Ask a Question

Qlik Sense Advanced Authoring

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

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ghaliabed
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
ghaliabed
Partner
Partner

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
Creator III
Creator III

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.

ghaliabed
Partner
Partner

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:

with only one measure definedwith only one measure defined

 

With Two Measures shows correctlyWith 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.