A member of the community recently ran into an issue where they needed to display week ending dates on a graph. How to show only the Week Ending Date with show missing values selection?
They were facing two problems
1. Their Fiscal Week start was Monday. So they needed to display the corresponding Sunday Date
2. They had sparse Data and needed to "Show Missing Values"
Why this was a problem?
1. When using the "Week Number" Datetrunc Option on the Canvas, Tableau will automatically set the Week Start to the Week Start configured in the Date Properties of the Workbook. This individual had already set the Date Properties to have a Starting Week Day of Monday. So no matter what we did, Tableau would force the "Week Number" Datetrunc to be the Monday date.
Exact Date -> Shows Desired End Week Dates [Sundays] (Calculated Field used DATEADD(‘day’,6, DATETRUNC(‘week’,[Order Date])) )
"Week Number Datetrunc" -> Forces it to the Monday Start Week as defined by the Date Properties.
2. We couldn't use Exact Date because he needed to "Show Missing Values". This caused Tableau to pad out every missing day, but we needed Tableau to only display missing weeks.
The first thing we needed to do was address the fact that Tableau does what Tableau does (It does not work always work the way we think it should). So since we know Tableau forces the "Week Number" Datetrunc to be the Starting Week Day configured in the Date Properties, we need to Set the Week Starting Date to be our desired "Week Ending Day". In this Case, Sunday.
The next thing we need to do is adjust our Dates so that when we Datetrunc them to Week Number, they fall into the correct weeks we wish to display.
DATEADD('week', 1, DATEADD('day', -1, [Date]))
By first Subtracting a Day, and then Adding a Week, we force the dates to line up to our desired Display Dates.
So Monday, Nov 2nd minus One Day = November 1st
November 1st Plus One Week = November 8th
November 8th Datetrunc to the Week with a Starting Weekday of Sunday = Sunday, November 8th (Our desired Week Ending Date).
Things to Note
Sometimes, in order to make Tableau Display what we want, we have to change our way of thinking. We have to think like Tableau (Because Tableau doesn't always work the way we think it should). It doesn't really matter what the exact Dates are from the Calculated Field above, what matter is how they get Truncated (And making sure that the Dates/Values line up in the weeks we expect them to when everything is said and done). So by understanding how Tableau Truncates the Date on the Canvas, and understanding our Underlying Data and Goals, we can manipulate things at will.
*This example works when you are Displaying "Week Number". Caution must be taken when trying to use the Calculated Field above in other forms of truncation, or calculated fields. This is a workaround to a specific type of problem, and not a end all, be all solution.*
Hope this helps.