Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm new to QlikSense and right now I am building a QlikSense Prototype. I am struggling with a year to year comparison.
I have created a Pivot table
As you can see I have 2 colums and multiple rows. What I want now is to compare "Anzahl FLT" for 2016 with 2015. I used "above" in the screenshot but this doesn't work when I break down the Pivot table into months.
So for example when i go to 01.01.2016 I want to see the percentage differance to 01/01/2015.
So first level of the hierarchy would be a comparison from 2016 - 2015
When the user opens up months it should show Month/2016 - Month/2015
And then the dates.
In a KPI I used this formula:
(sum({$<YEAR={'2016'}>}N_FLIGHTS)
- sum({$<YEAR= {'2015'}>}N_FLIGHTS))
/ sum({$<YEAR={'2015'}>}N_FLIGHTS)
This shows me the year to year differance in the KPI. But I can't insert the formula into the pivot table. In there I need a general formula.
I have asked somebody about this and the person said the formula needs to roll back 12 months back. But he couldn't tell me how that works.
Thank you for your help!
Here is what I proposed with the AsOfTable
New Script:
AsOfTable:
LOAD DISTINCT
[Date_Dimension] as AsOfDate,
Month([Date_Dimension]) as AsOfMonth,
Year([Date_Dimension]) as AsOfYear,
[Date_Dimension],
'Current Year' as Flag
Resident [DIM_DATE];
Concatenate(AsOfTable)
LOAD DISTINCT
[Date_Dimension] as AsOfDate,
Month([Date_Dimension]) as AsOfMonth,
Year([Date_Dimension]) as AsOfYear,
AddYears([Date_Dimension], -1) as [Date_Dimension],
'Previous Year' as Flag
Resident [DIM_DATE];
Dimension
AsOfYear
AsOfMonth
AsOfDate
Expressions
1) Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS)
2) If(Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS) > 0,
Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS)/Sum({<YEAR={'2015','2016'}, Flag = {'Previous Year'}>}N_FLIGHTS) - 1)
3) Sum({<Flag = {'Current Year'}>}N_LUGGAGE)
4) If(Sum({<Flag = {'Current Year'}>}N_LUGGAGE) > 0,
Sum({<Flag = {'Current Year'}>}N_LUGGAGE)/Sum({<Flag = {'Previous Year'}>}N_LUGGAGE) - 1)
You will have to use one of these two techniques
Hi Sunny,
I've read both articles but I don't really understand if and how this helps?
Since you have used YEAR as your dimension, set analysis is not going to work for you. Because set analysis will put the values for each of the expression in its corresponding year. To resolve your issue, either you have use Above() function or use the AsOfTable.
If you want more help, please post a sample or some sample data.
Best,
Sunny
Will take some time and I can work on this once I am home.
You're the best
Here is what I proposed with the AsOfTable
New Script:
AsOfTable:
LOAD DISTINCT
[Date_Dimension] as AsOfDate,
Month([Date_Dimension]) as AsOfMonth,
Year([Date_Dimension]) as AsOfYear,
[Date_Dimension],
'Current Year' as Flag
Resident [DIM_DATE];
Concatenate(AsOfTable)
LOAD DISTINCT
[Date_Dimension] as AsOfDate,
Month([Date_Dimension]) as AsOfMonth,
Year([Date_Dimension]) as AsOfYear,
AddYears([Date_Dimension], -1) as [Date_Dimension],
'Previous Year' as Flag
Resident [DIM_DATE];
Dimension
AsOfYear
AsOfMonth
AsOfDate
Expressions
1) Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS)
2) If(Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS) > 0,
Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS)/Sum({<YEAR={'2015','2016'}, Flag = {'Previous Year'}>}N_FLIGHTS) - 1)
3) Sum({<Flag = {'Current Year'}>}N_LUGGAGE)
4) If(Sum({<Flag = {'Current Year'}>}N_LUGGAGE) > 0,
Sum({<Flag = {'Current Year'}>}N_LUGGAGE)/Sum({<Flag = {'Previous Year'}>}N_LUGGAGE) - 1)
Hi Sunny,
thank you very much, it's working
If you have some spare time, would you mind explaining me the logic behind all of this?
You told me that SetAnalysis won't work since I'm using "YEAR" - why is that? (I tried to find a way 2 days straight). Why do I have to create a new table to be able to make the YoY-comparison?
In the script you are creating a new table "AsOftable" with all the date dimensions but what is the meaning of
'Current Year' as Flag
Resident [DIM_DATE]
Then you connect the new table "AsOfTable" if I understand correctly. What is the meaning of
AddYears([Date_Dimension], -1) as [Date_Dimension],
'Previous Year' as Flag
Resident [DIM_DATE];
Then I have some questions for the expressoins:
1) Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS) - why Flag?
2) If(Sum({<YEAR={'2015','2016'}, Flag = {'Current Year'}>}N_FLIGHTS) > 0 - what's the function of > 0 in this case?
3) 3) Sum({<Flag = {'Current Year'}>}N_LUGGAGE) - why did you use YEAR 2015,2016 for N_FLIGHTS and for N_LUGGAGE just Current Year?
I'm sorry that I'm asking so much but I just got started with QlikSense and all I had was the first introduction training.
Thank you again for you help and time!
Once again, some questions which might take some time to answer. But as before, I will try to respond them as soon as I get some free time
Lets start with seeing the relation between AsOfDate and Date_Dimension (and Flag)
See how each AsOfDate is associated with the same Date_Dimension where Flag = Current Year and with a date from one year ago when Flag = Previous Year.
Now, when I will use AsOfDate as dimension (and no set analysis) it will not just sum the value associated with that date, but also date associated with previous year's date. But to use this in our context, we use Flag is our set analysis to look at current year vs previous year against the current year's date (AsOfDate)
I think the above should address all the questions that you might have, but if you still feel that you don't understand something feel free to ask.
Also, I tried attaching back your sample with my last response, but somehow it did not get attach. So, I am attaching it with this response.
Best,
Sunny