9 Replies Latest reply on Apr 5, 2017 9:10 AM by Brad Sheridan

    Period Over Period LOD

    Doug Estes

      Tableau Community,

       

      I have been banging my head against a wall trying to figure out how to build a LOD calc to dynamically calculate year over year (ultimately will give the user the ability to choose month over month, week over week or year over year, but simply getting a year over year to work is a start). This is not being done statically from today’s date, but I want the user to be able to select any month they want.

       

      To define what I am considering year over year – selected month this year compared to same month in the previous year.

       

      I only want to show the change and not multiple time periods i.e. the user will select a single date to populate from. I need additional filters accounted for prior to calculating the amounts at the two time points. Ultimately the data will be graphed on a map.

       

      I built a small example using the super store sales data attached. I can correctly calculate current month, associated sales, and prior period but I cannot calculate prior period sales (note I will have additional filters which I have simulated using region in the workbook).

       

      I am able to build a parameter as the date filter and get the setup to work, but that leads to additional maintenance work every time the data updates as it won’t dynamically populate.

       

      Any help or guidance would be much appreciated!

        • 1. Re: Period Over Period LOD
          Daniel Teo

          Hi Doug,

           

          The best way to make YOY calculations is to use table calculations. I attached a workbook with the solution.

           

          Before going into the actual solution, it's always good to consider the order of operations:

          http://vizdiff.blogspot.co.uk/2015/10/evolution-of-order-of-operations-diagram.html

          A dimension filter happens before an EXCLUDE calculation. That's why your LOD calculation will not work, because you've already filtered out all the other months apart from November 2016.

           

          As it turns out, we don't need a LOD. What we need instead is a table calc filter which is way below on the order of operations schema. The schema is not complete, but for now it is enough to understand that a table calculation is done after Tableau retrieves the results. This means you actually have all the results from all the months in your data.

           

          You can use this formula to create a table calc filter:

          LOOKUP(MIN([Order Date]),0)

          Add the calculated field to the filter shelf. As with any other table calculation, make sure the 'Compute Using' is set to the correct field.

           

          For the Prior Year calculation, use the following calculation which basically just looks up the value 12 rows (or columns) prior to the current record.

          LOOKUP(SUM(Sales),-12)

           

          One caveat with this method: it obviously works for YOY on a monthly basis or WOW on a daily basis, since you can just lookup -12 or -7 respectively. This is a bit trickier for Month-over-Month, since not every month has the same number of days, but there are ways to get around that too.

           

          Daniel

          1 of 1 people found this helpful
          • 2. Re: Period Over Period LOD
            Norbert Maijoor

            Hi Doug,

             

            Find my approach as reference below and stored in attached workbook version 10.1

             

            • 3. Re: Period Over Period LOD
              Doug Estes

              Thanks Daniel, the solution worked perfectly. Your calculated date filter was the key I was missing to a table calc solution.

              • 4. Re: Period Over Period LOD
                Doug Estes

                Norbert,

                 

                Thanks for the reply. I liked your solution except the date filter using a parameter which is similar to the solution I had that would require manually updating as the data changes over time.

                • 5. Re: Period Over Period LOD
                  Norbert Maijoor

                  Hi Doug,

                   

                  Thanks for your reply. I understand your point but knowing upfront all individual dates until eternity;) gives you the possibility to define your parameter only one time based on a separate source with all individual dates for a certain range between two point in history and future.

                   

                  Have a nice one.

                  • 6. Re: Period Over Period LOD
                    Greg Blackshields

                    Hi Daniel

                     

                    i have a similar issue but i think it might be slightly different as it is based on a roll up of dates to a specific point in time this year vs a similar point in time last year.

                     

                    So for example I have ytd to the 13th Dec 2016, 125,000 in sales and i know the full year last year was 150,000 but at the 13th dec 2015, what was the value of sales.

                    I can exclude dates which is fine but i would prefer it to be dynamic and change each day as the prior days bookings this year gets added to the 125k.

                     

                    I think working day might work but unsure as to how to frame the calculation because my one is not as those above for a specific day or month or quarter but a roll up of days that need to match the same roll up of working days last year!

                     

                    Is this possible?

                     

                    Thanks

                     

                    Greg

                    • 7. Re: Period Over Period LOD
                      Daniel Teo

                      Hi Greg,

                       

                      This is possible indeed. When tackling a complex problem, it often helps to break it down into several smaller steps, each of which is a much simpler problem.

                       

                      1. Look up the maximum date in the report.

                      2. Extract the month and day from the maximum date.

                      3. Extract the month and day from the report date.

                      4. Create a filter that compares the two.

                       

                      See if you can devise your own solution based on the above, but here's the step-by-step of what I would do:

                       

                      1. Look up the maximum date in the report.

                      In this case, a simple Fixed LOD will do:

                      {MAX([Report Date])}. Let's call this one [Max Date In Report]

                      If you do not wish include the last day (as it might have only half a day of data), you could have a formula {MAX([Report Date])} - 1 instead.

                      Alternatively, you could use TODAY() (or TODAY() - 1) instead of the LOD, but that obviously depends on how your data is refreshed.

                       

                      2. Extract the month and day from the maximum date.

                      I don't believe there is a native function that extracts the month and day from a date. Therefore I use the following formula :

                      DATEPART('month', [Max Date In Report]) * 100 + DATEPART('day', [Max Date In Report]). Let's call it [Max Date In Report (MMDD)]

                      This will return the INTEGER 101 for Jan 1st... right up to 1231 to Dec 31.

                       

                      3. Extract the month and day from the report date.

                      DATEPART('month', [Report Date]) * 100 + DATEPART('day', [Report Date]). Let's call it [Report Date (MMDD)]

                       

                      4. Create a filter that compares the two:

                      [Report Date (MMDD)] <= [Max Date In Report (MMDD)]

                      If this condition is TRUE, then show the data. Otherwise exclude it.

                       

                      Daniel

                      • 8. Re: Period Over Period LOD
                        Greg Blackshields

                        Hi Daniel

                         

                        many thanks for this.

                         

                        I added it to my report a few days ago but thought best to track it over a few days to see if it worked.

                        I had to add one slight modification to allow it to concur with the working day tracking of my company but since that it looks to be working perfectly and tracks the numbers as expected.

                         

                        Thanks for providing the solution and breaking it down for me- the LOD as a starting point caught me- I need to buck up my understanding of LODs and how they can facilitate such calcs.

                         

                        Thanks again

                         

                        Greg

                        • 9. Re: Period Over Period LOD
                          Brad Sheridan

                          Thanks for posting the workbook Daniel...it helped a lot.

                           

                          Is there a way to have the numbers show for the last 3 months without having to select from a filter?

                           

                          I'm trying to create a column chart with bars/columns for the current month and prior 2 months.  So today, I would see a column for April, then February and January.  Simple enough using relative time

                           

                          However, within each bar, I also need a mark (I'm using a filled circle) for the same month, prior year amount

                           

                          Your solution seems to work but I don't want the user selecting from a filter...it should just show based on the system date

                           

                          thanks

                          Brad