
1. Re: YTD YoY calculation with a dynamic end date
james.diaz Mar 17, 2014 11:14 PM (in response to Eric Tobias)maybe a filter using a datediff calculated field can help.

2. Re: YTD YoY calculation with a dynamic end date
Michel Cavas Mar 18, 2014 2:25 AM (in response to Eric Tobias)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

Sample YTD CY vs YTD LY.twbx 34.1 KB

YTD YoY attempt 1.twbx 48.3 KB


3. Re: YTD YoY calculation with a dynamic end date
Prashant Sharma Mar 18, 2014 3:03 AM (in response to Michel Cavas)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,

4. Re: Re: YTD YoY calculation with a dynamic end date
Michel Cavas Mar 18, 2014 3:14 AM (in response to Prashant Sharma)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 Mar 18, 2014 3:44 AM (in response to Michel Cavas)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,

6. Re: YTD YoY calculation with a dynamic end date
Eric Tobias Mar 18, 2014 6:22 AM (in response to Michel Cavas)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 enduser 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 Mar 18, 2014 6:22 AM (in response to Michel Cavas)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 Mar 18, 2014 7:01 AM (in response to Eric Tobias)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,

9. Re: YTD YoY calculation with a dynamic end date
kettan Mar 18, 2014 7:16 AM (in response to Eric Tobias)1 of 1 people found this helpfulI think the data blending technique used in attached workbook gets the max date.
That said, I am not fond of this technique for getting "precalculated" max values and hope Tableau implements something better for this purpose such as:
Storing User Data Variables while doing Complex Calculations

10. Re: Re: YTD YoY calculation with a dynamic end date
Jonathan Drummey Mar 19, 2014 9:28 AM (in response to Prashant Sharma)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 nonNull marks:
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 selfjoin 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.

YTD YoY jtd.twbx 62.9 KB


11. Re: Re: YTD YoY calculation with a dynamic end date
Eric Tobias Mar 19, 2014 5:20 PM (in response to Jonathan Drummey)Thanks very much, Jonathan! That works like a charm!

12. Re: YTD YoY calculation with a dynamic end date
Jonathan Drummey Mar 20, 2014 2:53 AM (in response to Eric Tobias)You're welcome!

13. Re: YTD YoY calculation with a dynamic end date
Bethany Lyons Feb 2, 2015 1:41 PM (in response to Jonathan Drummey)1 of 1 people found this helpfulIn version 9 this is super simple with LOD calculations! {Max(Date)} returns the maximum date in the dataset!

14. Re: YTD YoY calculation with a dynamic end date
Keith Conner Mar 20, 2015 7:14 AM (in response to Michel Cavas)Thanks Michel!
Your solution solved a thorny issue for me!