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

    Year to Year Comparison Pivot table via Set Analysis

    Henry Tran

      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

       

      YoY.JPG

      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
          Sunny Talwar

          You will have to use one of these two techniques

           

          1) The As-Of Table

           

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

          • Re: Year to Year Comparison Pivot table via Set Analysis
            Sunny Talwar

            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];

             

             

            Capture.PNG

             

            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
                Henry Tran

                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
                Sunny Talwar

                Lets start with seeing the relation between AsOfDate and Date_Dimension (and Flag)

                 

                Capture.PNG

                 

                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
                    Henry Tran

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

                     

                    AsOfTable:

                    LOAD DISTINCT

                      [Date_Dimension] as AsOfDate,

                      Month([Date_Dimension]) as AsOfMonth,

                        Year([Date_Dimension]) as AsOfYear,

                      [Date_Dimension],

                        '2017' 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],

                        '2016' 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], -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
                        Sunny Talwar

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

                          • Re: Year to Year Comparison Pivot table via Set Analysis
                            Henry Tran

                            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:

                            LOAD DISTINCT

                              [DATE_DIMENSION] as AsOfDate,

                              Month([DATE_DIMENSION]) as AsOfMonth,

                                Year([DATE_DIMENSION]) as AsOfYear,

                              [DATE_DIMENSION],

                                '2017' as Flag

                            Resident [DIM_DATUM];

                             

                             

                            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],

                                '2016' as Flag

                            Resident [DIM_DATUM];

                             

                             

                            Concatenate(AsOfTable)

                            LOAD DISTINCT

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

                            LOAD DISTINCT

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

                            LOAD DISTINCT

                              [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