4 Replies Latest reply on Nov 13, 2015 8:24 AM by Simon Runc

    Finding Comparison Periods - Can my formula be improved

    Simon Runc

      Hi All,

       

      ...So following on from an unrelated post Using calculations 'on the left' - LoD I posted an example LoD I use to work out the first week of data I have, from the previous year...so that any YoY comparisons only include comparative weeks. Although it works...the calc is 'ugly' (not just aesthetically, but also it's not the most efficient) and am reaching out to anyone who can improve up on it. Rody Zakovich seemed up for the challenge!..as always!

       

      The situation we use this; When we get a new client, we often don't have more than 60ish weeks of data (we scrape data and the system we scrape only goes back 60 weeks). However we still want them to be able to use our YtD Comparison dashboard. So as well as knowing  the last week of data, we also need to calculate the first week of data (from the previous year) so we only compare 'comparative' weeks..suitably confused!...

       

      In the attached you'll see my current formula for this (there is also a similar one for picking the last week, but ignore that for now...that get's written into the data by our excellent DBA)

       

      [First Week Prev Year]

      INT(RIGHT(str({MIN(IIF([Year] = {MAX([Year])}-1,INT(str([YearWeek Int])),NULL))}),2))

       

      The below image (which is taken from the 'What's it doing' tab in the attached - where I've broken out the elements, using Tableau's very useful 'grab a bit of a calc field and drag it to the Viz'!). Here's a quick description;

       

      [Year] = {MAX([Year])}-1 //Gets the Previous Year

      {MIN(IIF([Year] = {MAX([Year])}-1,INT(str([YearWeek Int])),NULL))} //For Previous Year get the MIN of the YEARWEEK

      Finally Get the Right Two digits and turn them into an Int

      Comp Weeks Table.PNG

       

      What I like about this formula, and (ideally) needs to be maintained in any solution, is that results are returned at Row Level. This means I can use any VizLoD I want, and as you can see I've then created a 'calculated dimension' to apply

       

      [Time Period Class]

      IF [Week No Int] <= [First Week Prev Year] THEN 'No Comparison'

      ELSEIF [Week No Int] > [Last Week - Current Year] THEN 'Prev Year to Year End'

      ELSE 'Comparison'

      END

       

      I've also included a 'slimmed' down version of our final dashboard so you can see how we use it.

       

      I wrote this formula pretty quickly and was a 'pragmatic' solution at the time, so if anyone can think of a different way of accomplishing the same goal...it will be very much appreciated.

       

      Attached Workbook in T9.0

        • 1. Re: Finding Comparison Periods - Can my formula be improved
          Rody Zakovich

          Hey Simon,

           

          There may be an easier way to do this, but this is what I am thinking right now.

           

          [First Week Prev Year]

          INT(RIGHT(str({MIN(IIF([Year] = {MAX([Year])}-1,INT(str([YearWeek Int])),NULL))}),2))

           

          Here you have unnecessary casting and parsing. You are already identifying the specific [Year] via the IF/ELSE statement, so you don't need to do the additional steps of adding the YEAR and WeekNO together and then parsing. You can simplify it to

           

          [RZ - First week Prev Year]

          { MIN(IF [Year] = { MAX([Year]) -1 } THEN [Week No Int] END )}

           

          Same can be said for

           

          [Last Week Current Year]

          INT(RIGHT(STR({MAX (IIF([Year]={MAX([Year])},[YearWeek Int],NULL))}),2))

           

          This can be simplified to

           

          [RZ- Last Week Prev Year]

          { MAX(IF [Year] = { MAX([Year]) } THEN [Week No Int] END )}

           

          You final calc would remain the same. Here is my version.

           

          IF [Week No Int] < [RZ- First Week Prev Year] THEN 'No Comparison'

          ELSEIF [Week No Int] > [RZ - Last Week Current Year] THEN 'Prev Year to Year End'

          ELSE 'Comparison'

          END

           

          Here is a screen of what My "What It's Doing"

           

          11-13-2015 10-12-58 AM.png

           

          And the Final Viz - Line

           

          11-13-2015 10-13-31 AM.png

           

          You may be getting performance issues from the STR manipulations you are doing in the calcs. Generally speaking, it is much faster for Tableau to compare Numerical Data . Plus you don't have to do the additional parsing steps.

           

          Let me know what you think.

           

          Regards,

          Rody

          • 2. Re: Finding Comparison Periods - Can my formula be improved
            Simon Runc

            Thanks Rody,

             

            Nice work...Yes that makes complete sense (I had a 'general' feeling that I was casting one way then the other...and in fact in our 'real' world solution, there is a date-period selector ('Week', 'Month'...), so user can choose if they look by Week/Month..etc. (so if this is switched it has to do all the calculations again on 1st Month Last year...etc.). The original values are, also, dates and are then transformed into Week/Month..etc. values...so are further parsed...although from our previous discussion this one is pushed to TDE creation/refresh!.

             

            Just applied it to my 'master template' and definitely faster (+ more attractive, readable, debug-able...and all that good stuff ).

             

            Thanks a lot for looking into this for me...sometimes you 'can't see the wood from the trees' (especially I spend a lot of time immersed in this model)

            • 3. Re: Finding Comparison Periods - Can my formula be improved
              Rody Zakovich

              Simon, I completely understand what you mean. It's funny the quote you used here

               

              Simon Runc wrote:

               

              Thanks a lot for looking into this for me...sometimes you 'can't see the wood from the trees' (especially I spend a lot of time immersed in this model)

               

              I just responded to a question about an hour ago........

               

              "...Also, and this is from personal experience, jumping right into Advanced topics can have some negative effects, specifically, over-complicating calculations/problems. There are many times where you can get so into the details of how to do a table calc that you "Can't see the forest for the trees"

               

              Re: Am I ready for the Advanced Training?

               

              Great minds think alike

               

              Regards,

              Rody

              • 4. Re: Finding Comparison Periods - Can my formula be improved
                Simon Runc

                Great minds think alike

                 

                ...Yes I'll take that!!