6 Replies Latest reply on May 7, 2018 1:08 PM by Jim Dehner

    YTD and Last YTD Totals

    Bryce Kendall

      Hi all,

       

      I'm working with some data that comes to me in a packaged form every month. This means that the data has an end date that stays static until the next report comes in--fairly basic.

       

      In Tableau, I'm trying to work the data to give me a view that shows a Year-To-Date Total and a Previous-Year-To-Date Total. However, I'm running into some trouble because the calculations that I've seen used for Year-To-Date stuff involves a TODAY() function. This function makes the current YTD total work just fine, since the data ends anyways, but the PYTD calculation will keep running. An example sheet is attached with randomly generated values and a format that is similar to the original source, as far as the date column goes.

       

      Is there a way to get both of these calculations to use the ending date for the data instead of the TODAY() function so that they both calculate a running total which ends with the last date?

      (I though using MAX([Week Ending]) might work in place of that but it returns an AGG/Non-AGG error.)

       

      Thanks!

        • 1. Re: YTD and Last YTD Totals
          Joe Oppelt

          See attached.  (V 10.2)

          • 2. Re: YTD and Last YTD Totals
            Joe Oppelt

            What I did in there:

             

            I did a FIXED LOD to get the last date in the data source.  (I displayed that in the header.)

             

            Then I made a filter that gets months only up to the month of the last date in the data source.  (Regardless of year.)

             

            Now you don't need separate calcs for this year and last year.

            • 3. Re: YTD and Last YTD Totals
              Bryce Kendall

              Beautiful. This looks like it should work. I'll reply again after some testing.

               

              Thanks Joe!!

              • 4. Re: YTD and Last YTD Totals
                Bryce Kendall

                Seems I spoke too soon. The original data ends on a date that is sometimes early in the month, sometimes late in the month, for example, 4/2/18. The method you've used would be great if it always ended on the same date at the end of the month, but since there is overlap (and I'm not privy to when/how that date is chosen) the method you've used would calculate the total for April 2017 and for April 2018, even though 2017 would have much more sales recorded since the whole month is in the data set, as opposed to 2018 where only two days of transactions are recorded.

                 

                Also, the reason that I was wanting to use two different calculations was because I also wanted to chart the YTD %Diff in sales as well, but this is secondary to getting the format correct to begin with.

                 

                Back to the drawing board.

                • 5. Re: YTD and Last YTD Totals
                  Joe Oppelt

                  See attached.

                   

                  New strategy.  Still using [Last Date in data].  Now I went with your YTD calc, and made a parallel PYTD (for previous YTD) calc.

                   

                  The DATETRUNC function truncates a date to the beginning of whatever time period you specify.  So in this case the Match 31 date gets truncated to Jan 1 of the year in question.  So the YTD calc is pretty straightforward.  Grab from Jan 1 through the last date.

                   

                  In the PYTD I embedded a DATEADD function which adds whatever time period you specify.  In this case I specified 'year', and I added -1.

                   

                  So that calc grabs jan 1 of the prior year through whatever date was in the [Last] LOD calc of the prior year.

                   

                  One caveat.  Tableau's date functions are pretty powerful.  It even handles leap years.  But on the day that you have Feb 29 as your last date, I am not sure if you will get Feb 28 or March 1 as the end of range on your prior year.  Tableau will handle it, but I'm not sure what it will do there,  so if you ever will have that situation (in 2020), you might want to double check what you get from that.

                  1 of 1 people found this helpful
                  • 6. Re: YTD and Last YTD Totals
                    Jim Dehner

                    Hi Bryce

                    see the attached - this is a brute force way - using the max date in the data set

                     

                    It returns this  for your data

                     

                    here are the calculations involved (thy became more complex to get null values after the last month

                     

                     

                     

                    and the running totals need to be set as shown

                     

                    each is set independently

                     

                    Jim

                     

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.