Year to Year Comparison Pivot table via Set Analysis

[Anonymous]

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!

[sunny_talwar]

You will have to use one of these two techniques

1) The As-Of Table

2) Missing Manual - Above() and Below()

[Anonymous]

Hi Sunny,

I've read both articles but I don't really understand if and how this helps?

[sunny_talwar]

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

[sunny_talwar]

Will take some time and I can work on this once I am home.

[Anonymous]

You're the best

[sunny_talwar · Accepted solution]

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)

[Anonymous]

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!

[sunny_talwar]

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

[sunny_talwar]

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