topic Re: Calculated Difference between measures at chart level in App Development

Calculated Difference between measures at chart level

ghaliabed — Sat, 16 Nov 2024 07:03:06 GMT

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

	Actual	Budget	Var
Measure1	10	4	6
Measure2	5	5	0
Measure3	6	4	2

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 ?

Re: Calculated Difference between measures at chart level

ghaliabed — Fri, 14 Dec 2018 11:08:26 GMT

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

Re: Calculated Difference between measures at chart level

kaanerisen — Fri, 14 Dec 2018 13:15:29 GMT

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)
)

Hope it helps.

Re: Calculated Difference between measures at chart level

ghaliabed — Fri, 14 Dec 2018 14:01:33 GMT

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 defined

With 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.