September 17, 2012
Tableau Tip: Adding a moving reference line for “today”
Picture a sales chart like this that represents your actual + forecasted sales:
It’s difficult to see where today is in this chart, so you’d like to see it as a reference line on your time axis. And you want it to be dynamic so that it moves automatically every day. Ultimately, you want a chart like this and when you look at the chart again in a month, the reference line should be no whatever day that is, October 18 for example.
Step 1: Build the view so that the date field is on the Column shelf and the measure is on the Rows shelf. Right-click on your date pill and choose Exact Date.
Step 2: Create a calculated field to get today’s date.
Step 3: Add the Today field to the Level of Details shelf. Right-click on the pill and choose Exact Date. The pill will turn green, indicating it’s a continuous dimension.
Step 4: Right-click on the x-axis (i.e., time) and choose Add Reference Line.
Step 5: Because the Today field is on the Level of Detail shelf, we can use it as a Reference Line. Set up the reference line with these settings (format the line as you wish).
That’s it. The chart looks like this:
If you don’t believe that it works, change the clock on your computer. Of course, change the clock back when you’re done.
Download the sample workbook here.
Dan Murray left a great, incredibly useful comment to enhance the chart even more by color-coding actual and forecasted sales.
Step 1: Create a calculated field to determine whether Order Date is less today.
This results in a True/False dimension.
Step 2: Drag the Actual/Forecast dimension onto the color shelf.
Now you have a clear indication of where actuals stop and forecast starts, split exactly by the reference line.