4 Replies Latest reply on Oct 29, 2015 2:28 AM by mp.walker

    Rolling year to date and leap-years...how to aggregate?

    mp.walker

      I am using a moving calculation to display rolling year to date, and the client wants to be able to toggle between daily and monthly views.

       

      The "monthly" aggregation view would show for each month the total for the last 12 complete months (e.g. "December 2014" would show the cumulative total for dates 01/01/2014 - 31/12/2014 inclusive, and "November 2013" the cumulative total for dates 01/12/2012 - 30/11/2013 etc).

       

      Similarly, the "daily" aggregation would show the total for the past year to the specific date (e.g. "December 31 2014" would show the cumulative total for dates 01/01/2014 - 31/12/2014 inclusive, and "November 14 2013" the cumulative total for dates 15/11/2012 - 14/11/2013 etc). 

       

      The key bit is that when the client toggles between 'monthly' and 'daily' views, the "monthly" total reported for a given month should equal the "daily" total for the last day of that month i.e the monthly "December 2014" value = the daily "Decemebr 31 2014".

       

      The "daily" moving calculation is a sum of the previous 364 values plus the current value i.e. WINDOW_SUM(SUM([Total]), -364, 0);

      The "monthly" calculation is a sum of the previous 12 values up to end of previous full month i.e. WINDOW_SUM(SUM([Total]), -12, -1)

       

      The problem I have is with leap years: The "monthly" total reported for November 2012 would cover 01/12/2011 - 30/11/2012, which is a 366 day period (2012 being a leap-year) so it doesn't equal the "daily" total reported for November 30 2012.

       

      I need to get Tableau to change the "daily" moving calculation for dates between 29/02/2012 and 28/02/2013 so that it uses a 366-day year.  All atempts using 'If' and 'case' have yielded error message "cannot mix aggregated and non-aggregated".

        • 1. Re: Rolling year to date and leap-years...how to aggregate?
          Shawn Wallwork

          You probably knew this was coming, but please post a sample packaged workbook so we can better understand what you're situation actually is (Superstore dataset would be good for this one). In the meantime this calc will allow you to identify leap years:

           

          MAX(DATEPART('dayofyear', [Order Date]))

           

          2015-10-25_11-10-42.png

          But it will be dependent on how your viz is setup. Also let us know what version of Tableau you are using, as LOD expressions will probably play into the ultimate answer.

           

          Cheers,

           

          --Shawn

          • 2. Re: Rolling year to date and leap-years...how to aggregate?
            Robert Royer

            To me, it sounds like you want to know how many days in the PRECEDING year. Try this out.

             

            DATEDIFF('day',DATEADD('year',-1,[Date]),[Date])

            You may need to add MAX() or MIN() around the Date field depending on how you have your viz set up.

             

            Notice in the table below, as soon as you cross the leap day, the preceding year shows 366 days.

             

            Rolling Annual Days.PNG

            However, you probably wonder why leap day doesn't show 366 days. Let's bring in the DATEADD('year',-1,[Date]) portion next to the real date to see what value Tableau is calculating as the One Year Ago date:

            One Year Ago.PNG

            All the values show the exact day but one year ago, except of course leap day. Let's add a catch for leap years (you could do this a bunch of ways, here's one):

            Correct Days.PNG

             

            I think this helps answer the concept, but you will need to apply the concept to your level of detail of table functions. As shawnwallwork suggested, please post your workbook and we will be happy to help further.

            • 3. Re: Rolling year to date and leap-years...how to aggregate?
              Shawn Wallwork

              Yo MP! These are some interesting replies. Just curious if you're coming back to let us know what is/isn't working for you. We helpers are Catnip junkies! So to speak.

              • 4. Re: Rolling year to date and leap-years...how to aggregate?
                mp.walker

                Attached is a simplified workbook (tableau 9.0) that demonstrates the issue.

                 

                Toggle the 'Date Grouping' between months and days to change the dates presented in the table.

                 

                When "months" is selected, the table shows the rolling 12-month total up & including the given month (date entries displayed as end of month date eg. 30 June, 31 July etc). When "days" is selected, the table shows the rolling 365-day total up & including the given day (date entries displayed as day dates eg. 19 June, 20 June etc)

                 

                The desired behaviour is that the value seen for the dates when in "month mode"  should equal those of equivalent date in the "day mode" i.e. the end-of-month date in each view of the date should be the same.  And this is how it works except for leap-years when the rolling 12-month total ("month mode") will include data for 366 days, while the "day mode" totals only have 365 days of data.

                 

                As an example, the table entry for 31 August 2014 in "month mode" equals the table entry for 31 August 2014 in "day mode".  However, the table entry for 31 December 2012 in "month mode"  does NOT equal the table entry for 31 December 2012 in "day mode".

                  

                Need to have Tableau dynamically change the range of rolling calculation to have it include 366 records in the day-level calculation when in a leap year.