What if I’m an impatient manager and want to see the current results mid-week? Let’s use Tableau to create a visualization where we can check if our Sales Team needs a bit of motivation.
This trick is useful when day-to-day performance tracking is required, but the distribution of performance is not uniform throughout the week.
I’m going to use the Superstore dataset for the visualization.
Create a parameter that will store the date you want as the end date of your comparison. I’ll call it ‘Latest Date’. For real dashboards and visualizations you might use the TODAY() function instead of this, but since I’m using the Superstore dataset which does not have a date field that extends until today, I’m going to use this parameter instead.
Create a calculated field that will tell us the start of the most recent week. I’ll call it ‘Latest Week’
Create a calculated field that will tell us the start of the previous week. ‘Previous Week’
Nothing crazy so far, but here comes the magic. We need a calculated field that will calculate our metric but only for days that are between Monday and the current weekday, but from last week. Since this is a nested conditional we’ll do it in multiple steps, but you could include both condition in a single calculated field.
Let’s create two booleans: one for seeing if the purchase has happened last week, and another for seeing if the purchase has happened in the same part of their week as we are in right now (i.e. if today is Wednesday and the purchase happened on Tuesday it’s okay, if it happened on Thursday it’s not). The second one does not take into account which week the purchase has happened on, that will happen when we take the two booleans in combination.
First boolean called ‘Happened last week’:
Second boolean called ‘Happened this part of the week’:
Let’s combine these two and give our desired metric at the end. I’ll call this ‘Previous W2D Sales’:
After this you can use this measure anywhere. I created this visualization looking at this week’s sales and comparing their running sum to last week’s. It displays the result on a bar chart in the background.
Credit to Elliott Stam and his similar article. His solution is a bit more complicated on the boolean part, but is worth checking out.