3 Replies Latest reply on Dec 7, 2018 9:51 AM by Jennifer VonHagel

    Compare Year over Year Quarterly results

    Paddy Kavanagh

      Hi all,

       

      I hope someone can help - I don't see it already asked.

       

      I have quarterly data for each quarter. The quarter data looks like this in the 'Quarter' Column:

      2017 Q1, 2017 Q2, 2017 Q3, 2017 Q4, 2018 Q1, 2018 Q2, 2018 Q3

       

      The data is automatically fed, so every Quarter I would like tableau itself to pick out the Sum([Sales]) for the last quarter and the 5th last quarter

      e.g. display '2018 Q3' and '2017 Q3'

      and when I get '2018 Q4' data fed into the dashboard, I'd like it to automatically switch over to display '2018 Q4' and '2017 Q4'

       

      Having Tableau display a quarter over quarter display is easy - By selecting the Top 2  to be displayed but this quarterly Y/Y comparison seems to be challenging

        • 1. Re: Compare Year over Year Quarterly results
          Jennifer VonHagel

          Hi Paddy, here are a few options.

           

          Option 1: Turn your Quarter into a date in Tableau, and create a calculation to find the latest Quarter in your data and the corresponding prior year quarter.

          1. Change the datatype of Quarter from String to Date - Tableau will take care of the rest. In the left pane, find the Quarter field, click the "Abc" data type on the left and choose "Date".

          2. Create a calculation to call out the time periods you want to see:

          In the following calculation, the { FIXED : MAX([Quarter Date]) } calculation allows you to reference the MAX Quarter across the entire data set, even on rows where the quarter is not the MAX quarter.  So we can check your row of Quarters and test whether each quarter is equal to the { FIXED : MAX(Quarter) } and label it accordingly.  The second part of the calculation uses the DATEADD() function to find the date one year prior to the { FIXED : MAX([Quarter Date]) }.

           

          Time Periods (Dt):

          IF [Quarter Date] = { FIXED : MAX([Quarter Date]) }

          THEN 'Latest Quarter'

          ELSEIF [Quarter Date] = DATEADD('year',-1,{ FIXED : MAX([Quarter Date]) })

          THEN 'Prior Year'

          ELSE 'Exclude'

          END

           

          You can put Time Periods (Dt) on the Filter and exclude the "Exclude" values, leaving only the two dates you care about in the view:

           

           

           

          Option 2: If for some reason you need to leave your Quarter as a string, you can use string manipulation functions to find the latest Quarter in your data and the corresponding prior year quarter.  Because your quarters are in format Year QQ, looking for the max(Year QQ) will yield the latest one.

           

          In the following calculation, the { FIXED : max(Quarter) } calculation allows you to reference the MAX Quarter across the entire data set, even on rows where the quarter is not the MAX quarter.  So we can check your row of Quarters and test whether each quarter is equal to the { FIXED : MAX(Quarter) } and label it accordingly.  The second part of the calculation tests whether the first 4 characters (year) of each quarter is = the first four characters of the { FIXED : MAX(Quarter) }'s year minus one (last year), AND checks that the right most character (1,2,3, or 4) is equal, as we want the same quarter of the prior year.

           

          Time Periods:

          IF [Quarter] = { FIXED : MAX([Quarter]) }

          THEN 'Latest Quarter'

          ELSEIF

              //Year of Quarter = Year of MAX Quarter - 1

              INT(LEFT([Quarter],4)) = INT(LEFT({ FIXED : MAX([Quarter]) },4)) - 1 AND

              //Quarter of Quarter = Quarter of MAX Quarter

              RIGHT([Quarter],1) = RIGHT({ FIXED : MAX([Quarter]) },1)

          THEN 'Prior Year'

          ELSE 'Exclude'

          END

           

          Workbook is attached.

           

          Best,

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: Compare Year over Year Quarterly results
            Paddy Kavanagh

            This is awesome Jennifer  - Thank you!! This has answered my question

             

            Because I'm dealing with Fiscal Quarters I'll stick with the Option 2

             

            With this method, is it possible to pick out the 'Prior Quarter' -

            The reason for this is, after looking at your explanation of how it works, I'm thinking of creating a parameter so the dashboard user has an option to compare quarters that are either Q/Q or Y/Y

             

            Thank you again!

            • 3. Re: Compare Year over Year Quarterly results
              Jennifer VonHagel

              Hi Paddy, sure:

               

              Since we'll be referencing the Latest Quarter all the time, I'm going to go ahead and make it it's own calculation to make writing other calcs a little simpler:

              I've added Prior Quarter into the Time Periods (Str) IF statement.

               

              To translate to English, if the Latest Quarter's quarter is Q1, then we want to find the quarter with Latest Quarter's year-1 and necessarily Q4.  If the latest quarter is not Q1, we can use the same year as Latest Quarter and find Latest Quarter's Qx - 1.

              IF [Quarter] = [Latest Quarter]

              THEN 'Latest Quarter'

              ELSEIF //Get same quarter last year

                  //Year of Quarter = Year of MAX Quarter - 1

                  INT(LEFT([Quarter],4)) = INT(LEFT([Latest Quarter],4)) - 1 AND

                  //Quarter of Quarter = Quarter of MAX Quarter

                  RIGHT([Quarter],1) = RIGHT([Latest Quarter],1)

              THEN 'Prior Year'

              ELSEIF //Get prior quarter

                  //Prior Qtr YEAR: If Q1, find prior year, else find same year

                  INT(LEFT([Quarter],4)) =

                      IF INT(RIGHT([Quarter],1)) = 1

                      THEN INT(LEFT([Latest Quarter],4)) - 1

                      ELSE INT(LEFT([Latest Quarter],4))

                      END

                  AND

                  //Prior Qtr QUARTER: IF Q1, look for Q4 else look for Qx - 1

                  INT(RIGHT([Quarter],1)) =

                      IF INT(RIGHT([Latest Quarter],1)) = 1

                      THEN 4

                      ELSE INT(RIGHT([Latest Quarter],1)) - 1

                      END

              THEN 'Prior Quarter'

              ELSE 'Exclude'

              END

               

              Best,

              Jennifer