1 2 Previous Next 21 Replies Latest reply on Dec 20, 2016 12:31 PM by Ron Eisenstein

    YTD YoY calculation with a dynamic end date

    Eric Tobias

      I have a data source that updates on different days each week. I need to calculate the highest available date in the data, then compare the YTD through that date and compare to the previous year, same period.

       

      So for example, assume the highest date in the sales data is March 3, 2014 I would want the YTD for the current year to be Jan 1 through March 3, 2014. I would want to compare that to Jan 1 through March 3, 2013.

       

      I can do this if I use a parameter and have the end-user pick a "through date", but no one on my team can figure out how to do this based on the latest date in the underlying data for the current year.

       

      How can this be accomplished? I've attached a packaged workbook with a version of Superstore that has data through May 5, 2012. I'd like a way to show total sales from Jan 1, 2012 through May 5, 2012 and total sales from Jan 1, 2011 through May 5, 2011. Again, the solution needs to dynamically identify the latest date in the data.

       

      Tableau Support gave me some things to try including "LAST" and WINDOW_MAX, but neither of the solutions would work when doing year-to-year reporting.

        • 1. Re: YTD YoY calculation with a dynamic end date
          james.diaz

          maybe a filter using a datediff calculated field can help.

          • 2. Re: YTD YoY calculation with a dynamic end date
            Michel Cavas

            Hey Eric

             

            This is a rigid and at the same time dynamic way of calculating what you want, but it should work. Because your workbook does not contain any data past 2012, you will not see any values in this example workbook. Assuming you do actually have data for 2014 and 2013 in your database, it should provide you with the YTD Current year vs YTD Last year comparison without having to use a parameter. This calculation has the today() function incorporated, which means it will always use all the data available until and including today.

             

            I included your sample workbook, but as noted there is no YTD for current year or last year, as that data is not available in your sample dataset.

            So I made an excel sheet with some sample data starting in 2013 and going up to 2015, and filled it for the entire period with data. This will allow you to see more detailed how these two calculated fields I added will function. This is the "Sample YTD CY vs YTD LY.twbx". Drill down to the day level for date, and you can see it will actually perform the calculation you want. Remove the date dimension from the shelf to get your YTD values.

             

            Kind regards,

             

            Michel

            • 3. Re: YTD YoY calculation with a dynamic end date
              Prashant Sharma

              Hi Michel,

              I think you have changed the requirement. Eric is looking for some MAX function for finding the maximum date to calculate the YTD. I think he does not want to use today() & want to replace it with some date which is max([date]).

              I think for this you have to extract one date field from your database which is the latest date field as we can not use max([date]) in the current YTD formula. You can use custom SQL for this.

              Also, Datediff calculated field in filter does not help in this because he is looking for prior year sales too.

               

              Warm Regards,

              Prashant Sharma - India | LinkedIn

              • 4. Re: Re: YTD YoY calculation with a dynamic end date
                Michel Cavas

                I took this from Eric:" I have a data source that updates on different days each week. I need to calculate the highest available date in the data...."

                 

                In my understanding this means that the max date will always be today? Unless these updates include future data?

                So unless the updates introduce future data, then this calculation should still work.

                • 5. Re: YTD YoY calculation with a dynamic end date
                  Prashant Sharma

                  Hi Michel,

                  Yes, you are right & thanks for correcting me as I have missed that line.

                  I am elaborating this again. Eric, let me know where I am wrong.

                  If you want to show the sales till the latest date (as this is updating every week) then what Michel suggest will work. If you have something like closed date & wants to show sales till your last closing date then you can use closing date field or some other field which can describe your latest date or something like closing date for sales in your YTD formula.

                   

                  Warm Regards,

                  Prashant Sharma - India | LinkedIn

                  • 6. Re: YTD YoY calculation with a dynamic end date
                    Eric Tobias

                    Thank you for the feedback, Michel, however that won't work. I'd already tried that. It breaks the requirement to dynamically identify the latest data date in the underlying data. That's why I didn't include any data for 2014, so the solution would "break" if attempting to use the "today" approach.

                     

                    The problem with using "today" is that it causes the "prior YTD" calculation to be off. Let's say the data was last refreshed on March 15th, 2014. If we use "today" we would correctly get the current YTD data date as March 15th. However, the "today" calculation would actually return March 18th... it's just that there's no YTD data from March 15th to March 18th, which is why it correctly calculates the total.

                     

                    If we sum up last year's data based on "today" we'd get data through March 18th, 2013, where there *IS* data. It is three days longer than the current YTD. So we'd be comparing 74 days in 2014 to 77 days in 2013. While they're not off by much, they're still off and would cause the end-user to misinterpret the growth numbers.

                     

                    So whatever the solution to this is, it can't be based on "today". It has to be based on MAX( [order date] ) in some way. Hopefully that makes sense. Let me know if I've misinterpreted your proposed solution in some way.

                    • 7. Re: YTD YoY calculation with a dynamic end date
                      Eric Tobias

                      The max date will not always be today. The max date will always be a number between 1 and 7 days from today.

                      • 8. Re: YTD YoY calculation with a dynamic end date
                        Prashant Sharma

                        Hi,

                        I think the only option for this is to create a date through custom SQL or by creating a date which describes that which date is the maximum date. We can not use Max here due to aggregation. But may be someone from community is having solution for this. Jonathan Drummey may help into this if he is available.

                         

                        Warm Regards,

                        Prashant Sharma - India | LinkedIn

                        • 9. Re: YTD YoY calculation with a dynamic end date
                          kettan

                          I think the data blending technique used in attached workbook gets the max date.

                           

                          That said, I am not fond of this technique for getting "pre-calculated" max values and hope Tableau implements something better for this purpose such as:

                           

                          Multi-Source Functions

                          Storing User Data Variables while doing Complex Calculations

                          Use table worksheet as a datasource

                          1 of 1 people found this helpful
                          • 10. Re: Re: YTD YoY calculation with a dynamic end date
                            Jonathan Drummey

                            kettan had one potential solution using data blending, I've attached a solution using table calculations. The first is using TOTAL(MAX([Order Date])) for the Latest Order Date, an alternative would be PREVIOUS_VALUE(WINDOW_MAX(MAX([Order Date]))). Either way they will get you the latest Order Date, the TOTAL() will most often issue a separate query to the data source so it may be slower, but it's fewer calcs inside Tableau.

                             

                            The key calculation is a single Either Year Flag with the following formula:

                             

                            //current year

                            YEAR(MIN([Order Date])) == YEAR([Latest Order Date])

                            OR

                            //prior year

                            (YEAR(MIN([Order Date])) == YEAR([Latest Order Date])-1

                            AND MIN([Order Date]) <= DATEADD('year',-1,[Latest Order Date]))

                             

                            The Compute Using for this calc is the Order Date.

                             

                            Because this is a table calc, if it is used as a regular filter then it won't work because the rest of the computations will have been completed. So we have to embed that inside a calculation to get a single Sales for YTD measure, such as:

                             

                            IF FIRST()==0 THEN

                                WINDOW_SUM(IF [Either Year Flag] THEN SUM([Sales]) END)

                            END

                             

                            Then I set up the view with the YEAR(Order Date) and DAY(Order Date). Tableau will let us have the nested compute using where the inner Either Year Flag has is addressing on the entire Order Date, and the Sales for YTD has an Advanced Compute Using on Day of Order Date (so it partitions the WINDOW_SUM on the Year). You can see all this in the workout crosstab.

                             

                            Finally, I put DAY(Order Date) on the Level of Detail Shelf and turned off the tooltips (since that's only needed to get the calculations right), and then Ctrl+Dragged a copy of the Sales for YTD calc onto the Filters Shelf to filter for non-Null marks:

                            2014-03-19 12_24_47-Tableau - YTD YoY jtd.png

                             

                            If I were to use this in a production, I'd also add a relative date filter on Order Date to only get the last two years worth of data, or maybe 25 months, that way Tableau isn't pulling data for every distinct Order Date in the data set.

                             

                            I've had to solve this problem for myself multiple times, since I'm the one writing the queries to pull the data in the first place usually I do a self-join to get this information. When the Max Date is available as a dimension instead of an aggregate (as in the data blend) or a table calc (as in what I've posted here), there's more ease of construction in Tableau. That said, if you want to do complex filtering operations then you'd probably need to use the data blend or table calc route.

                            2 of 2 people found this helpful
                            • 11. Re: Re: YTD YoY calculation with a dynamic end date
                              Eric Tobias

                              Thanks very much, Jonathan! That works like a charm!

                              • 13. Re: YTD YoY calculation with a dynamic end date
                                Bethany Lyons

                                In version 9 this is super simple with LOD calculations! {Max(Date)} returns the maximum date in the dataset!

                                1 of 1 people found this helpful
                                • 14. Re: YTD YoY calculation with a dynamic end date
                                  Keith Conner

                                  Thanks Michel!

                                   

                                  Your solution solved a thorny issue for me!

                                  1 2 Previous Next