18 Replies Latest reply: Jan 11, 2017 2:27 PM by Sunny Talwar

# Year to Year Comparison Pivot table via Set Analysis

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!

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

You will have to use one of these two techniques

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Hi Sunny,

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

You're the best

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Here is what I proposed with the AsOfTable

New Script:

AsOfTable:

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

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

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!

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

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

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

If I'm using more years instead of just 2, can I use the following script:

AsOfTable:

[Date_Dimension] as AsOfDate,

Month([Date_Dimension]) as AsOfMonth,

Year([Date_Dimension]) as AsOfYear,

[Date_Dimension],

'2017' as Flag

Resident [DIM_DATE];

Concatenate(AsOfTable)

[Date_Dimension] as AsOfDate,

Month([Date_Dimension]) as AsOfMonth,

Year([Date_Dimension]) as AsOfYear,

AddYears([Date_Dimension], -1) as [Date_Dimension],

'2016' as Flag

Resident [DIM_DATE];

Concatenate(AsOfTable)

[Date_Dimension] as AsOfDate,

Month([Date_Dimension]) as AsOfMonth,

Year([Date_Dimension]) as AsOfYear,

AddYears([Date_Dimension], -2) as [Date_Dimension],

'2015' as Flag

Resident [DIM_DATE];

what I changed in here is instead of "Current Year" and "Previous Year" I'm not using 2017, 2016, 2015 and in the line

AddYears([Date_Dimension], -1) as [Date_Dimension]

-1 for 2016

and -2 for 2015. Would that be correct?

The first expression would change to

Sum({<YEAR={'2015','2016','2017'}, Flag = {'2017'}>}N_FLIGHTS)

right?

Thanks a lot for you help

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Yes looks great to me. I am glad you picked this up really fast

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Hi Sunny,

short Question for the expressions where the %-difference is calculated:

I have no several years and want to show the %-difference from each year to the year before. so 2017 difference to 2016, 2016 difference to 2015, 2015 -> 2014

The script looks like this:

AsOfTable:

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

[DATE_DIMENSION],

'2017' as Flag

Resident [DIM_DATUM];

Concatenate(AsOfTable)

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

AddYears([DATE_DIMENSION], -1) as [Date_Dimension],

'2016' as Flag

Resident [DIM_DATUM];

Concatenate(AsOfTable)

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

AddYears([DATE_DIMENSION], -2) as [Date_Dimension],

'2015' as Flag

Resident [DIM_DATUM];

Concatenate(AsOfTable)

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

AddYears([DATE_DIMENSION], -3) as [Date_Dimension],

'2014' as Flag

Resident [DIM_DATUM];

Concatenate(AsOfTable)

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

AddYears([DATE_DIMENSION], -4) as [Date_Dimension],

'2013' as Flag

Resident [DIM_DATUM];

how does the measure look like when I have more then 2 years?

I tried this but it's not really working:

If(Sum({<YEAR={'2013','2014','2015','2016','2017'}, Flag = {'2017'}>}N_FLIGHTS) > 0,

Sum({<YEAR={'2013','2014','2015','2016','2017'}, Flag = {'2017'}>}N_FLIGHTS)

/Sum({<YEAR={'2013','2014','2015','2016','2017'}, Flag = {'2016'}>}N_FLIGHTS) - 1)

Thanks Sunny

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Would you mind sharing the updated qvf file for me to look at this?

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

There is an error here:

AsOfTable:

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

[DATE_DIMENSION],

'2017' as Flag

Resident [DIM_DATUM];

Concatenate(AsOfTable)

[DATE_DIMENSION] as AsOfDate,

Month([DATE_DIMENSION]) as AsOfMonth,

Year([DATE_DIMENSION]) as AsOfYear,

AddYears([DATE_DIMENSION], -1) as [Date_Dimension] [DATE_DIMENSION],

'2016' as Flag

Resident [DIM_DATUM];

Same issue for the other Concatenations.

Can you fix this and reload to see if it fixes your issue

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Should be correct now

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

I think you only have BU_BU_WERY_SUM for 2017 and 2016, so the chart is only able to work for 2017. But one thing I noticed was that you were using DD_JAHR as your dimension and you need to use AsOfYear, AsOfMonth, and AsOfDate as your dimensions.

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

Correct I only have data for 2016/2015.

Thank you you are right I only have data for 2016/2015. So this means if I get later date for the other years i might have to write you again for your knowledge

I have some troubles confirming the the values in my KPI.

I noticed that in the measures "Year" has no affect.

I created two KPIs

one with Sum({<Year = ('2016')>} N_FLIGHTS)

the strange thing is, the KPI doesn't show me amount of N_FLIGHTS for the Year 2016. It shows me the total amount of N_FLIGHTS. It doesn't matter if {<Year = ('2016')>}  is in it.

What I'm trying to do is to validate the numbers inside of the pivot table by looking at the sum N_FLIGHTS for a month of a year and dividing that through the sum BU_BU_WERY_SUM of the same month and year.

But it somehow doesn't seem to work with "Year" and "Month"

Do you know why?

• ###### Re: Year to Year Comparison Pivot table via Set Analysis

What is difference between Year and DD_JAHR? May be you need to use DD_JAHR

Sum({<DD_JAHR = ('2016')>} N_FLIGHTS)