I originally posted this as a question: Create Week Days for Current Week Using Today() Function, but after a few days with no response, I had no choice but to slog it out until I came up with a solution. Fortunately, I did! I'm sure it was a much bigger moment for me than it would be for most other people, but hey! I felt great about it...
I've since tweaked the original solution; as it presented a few hiccups when incorporating it into more complex charts. It also turned out to have a lot of unnecessary extra steps. But here you go, streamlined and much simpler!
This tutorial will outline:
How to create a day planner for the current week, showing the number of appointments and / or the value of all appointments for each day - and is able to show any days of the week on the axis, even when there are no appointments for that day (i.e. the date does not exist in the database). So essentially, we'll be creating dates for the current week inside of Tableau using the TODAY() function, independent of the actual datasource. We'll then pull values from any corresponding dates in the dataset that match our generated dates.
The setup itself is surprisingly simple; really all we need to do is create one calculation for each day of the week (Sunday if it's required), and we're all set:
[Monday Appointment Values]:
IF DATE([Scheduled Date]) = DATETRUNC('day',DATETRUNC('week',TODAY()))
THEN [Price] ELSE 0 END
[Tuesday Appointment Values]:
IF DATE([Scheduled Date]) = DATETRUNC('day',DATETRUNC('week',TODAY())+1)
THEN [Price] ELSE 0 END
And so on... until you reach [Saturday]
Then we simply drag 'Measure Values' onto Rows and 'Measure Names' onto Columns, edit the alias of each measure to represent the corresponding day of the week, et voila!
If you want to show number of appointments per day, alter the calculations to show DATE([Scheduled Date]) instead of [Price] and change the aggregation of the 'Measure Values' to COUNT. Remember to edit their aliases as well.
As different measures can't have the same alias, I just use variations of "Monday", " Monday ", " Monday", "Monday " - but you get the picture! If you have as many OCD tendencies as I do, then those last two options will likely drive you up the wall...
Depending on what you want the start of the week to be, don't forget to define the starting day under Data > Date Properties (as the calculations we created will work off of this logic when defining days of the week):
The only downside of attaching the solution Workbook here is that it uses a static dataset and as the worksheets are based on current week info, the data might be outdated by the time you view it! But that's why I'm attaching the sample data as well so you can just modify it to reflect more recent dates.
Anyway, hopefully this helps somebody!