Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a measure to show the variance/difference between 2 periods.
For example, I like to show the difference between 2015Q1 vs. 2016Q1. What's the right expression required in creating such measure in Qlik Sense?
Please see attached.
Ideally, I am trying to configure to look something like the following:
Jan
Feb
Item Category
2015
2016
Variance
Variance %
2015
2016
Variance
Variance %
Chair
100
120
20
20%
150
270
120
80%
[Item Category] is a dimension. You can't perform a Sum on Dimension, you can perform a count on it but it isn't a numeric value that you can perform addition on.
The Measure that you are using in the Table is [QTY]. This is what you want to look at.
Within your load script, the autocalendar created the year but for some reason, it's not allowing calculations to be performed. To correct for that, create your own calendar.
For example - go into the Data Load Editor and 'Unlock' the script. Add the following lines under [Posting Date]:
LOAD [Posting Date],
year([Posting Date]) as [Posting Year],
month([Posting Date]) as [Posting Month], ...
Second, create a new Master Measure and call it CurrentYear. Use the following syntax: sum({<[Posting Year] = {$(=max([Posting Year]))}>} QTY)
Test this in a new KPI Object and make sure it returns data. If it does, you can create other measures to leverage.
If you want to see CurrentYear and PriorYear side by side in a KPI, create a second Master Measure called PriorYear and use this syntax:
sum({<[Posting Year] = {$(=max([Posting Year])-1)}>} QTY)
If you just want the variance, create another Master Measure subtracting one from the other:
sum({<[Posting Year] = {$(=max([Posting Year]))}>} QTY) - sum({<[Posting Year] = {$(=max([Posting Year])-1)}>} QTY)
Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.