5 Replies Latest reply on May 27, 2016 8:54 AM by David Heinz

    Rolling like4like calculation

    David Heinz

      Hi guys,

       

      I want to compare the cumulated actual years' sales (from january 1st until today) with the cumulated previous years' sales (from january 1st until may 27th/2015).

      Further, the calculation should automatically refresh every day. If I look at the data tomorrow, tableau should show me the like4like sales from january 1st until may 28th.

      I thought about using the lookup function on a week and day basis. Would that be the right way?

       

      Best, David

        • 1. Re: Rolling like4like calculation
          Carl Slifer

          Howdy David,

           

          You may find a lot of value in reading and using this blog that I've created: Date Comparisons in Tableau Made Easy

          In order to get the data to and the charts to update based on todays date the field to reference will be TODAY(). In this blog I allowed the user to either choose to use the current data or any other date in time. Please check it out and especially play with the dashboard at the bottom to get an idea how it works.

           

          Cheers!

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful
          • 2. Re: Rolling like4like calculation
            Michael Hesser

            Carl Slifer this is awesome!

             

            I knew there had to be a solution to David's problem that was more elegant than "banging it with a hammer."

             

            Great stuff!

             

            Bookmarked!

            • 3. Re: Rolling like4like calculation
              David Heinz

              That's amazing Carl!

              Exactly what I was searching for!

               

              I just got stuck with the calculations. Should I create three different calculated fields? One for each analysis type?

              And do you start the calculations with "WHEN "YoY" THEN" or with "IF DATEDIFF..."?

               

              I guess for the DATE in the calculations I should always take the invoice date of the KPI's I use?

               

              Do I have to drag all the created parameters and calculated fields to the marks shelf and and display the quick filters to develop the same viz like you did in your blog?

              • 4. Re: Rolling like4like calculation
                Carl Slifer

                Hi David,

                 

                I calculated field per result is what I would suggest. But if you want to be able to test each one more easily I would build them separate and then combine them.   It depends what calculation that you are referring too.

                 

                I used

                CASE [ThisField]

                WHEN 'Option1' THEN 'Result 1'

                 

                I did this to be cleaner and so that I did not have to write extra calcs. Case statements tend to be quicker if you have a fine number of returns but IF works better for ranges or data that isn't an easy to choose option.

                 

                Again I'm not sure what your data looks like so I'd be hesitant to say for sure how to use the date calculations. I would say that when comparing dates keep your dates constant betweeen KPIs ( use invoice date for everything if it fits).

                 

                In order to duplicate exactly what I've done - yes you will need to use the same objects on the shelves and the same parameters in use.  By using the marks shelf I was able to color my marks and add extra levels of detail to the graph that was not created with the rows and columns shelf.

                 

                Hope this helps

                Cheers!

                Carl Slifer

                InterWorks

                • 5. Re: Rolling like4like calculation
                  David Heinz

                  Hi Carl,

                   

                  somehow it doesn't really work...

                  I attached a WB of my calculations with the superstore Data.

                   

                  Can you explain the mistake I obviously make?

                   

                  Again, thanks a lot!