Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
ghaliabed
Partner - Creator
Partner - Creator

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 (3)
3 Replies
ghaliabed
Partner - Creator
Partner - Creator
Author

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 - Creator
Partner - Creator
Author

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.