Using only One Parameter, One Calculation, and Formatting
The Problem
I came across this issue and use case in one of my projects at work. I had an excel file with several metrics where the projected data varied by year. My data was hardcoded (the forecasting feature in tableau was not utilized).
First, I wanted to easily differentiate each metric’s projection year(s) on a line graph, either by highlighting, recoloring, changing the line style, etc. I quickly realized this was easier said than done in tableau.
Second, I wanted that highlighted/differentiated year(s) to update depending on the metric I selected, because some of my metrics only had one year of projections while others had two. That was a whole other hurdle.
After several google searches with no quick or uncomplicated solution, I started playing around in Tableau. There are a few workarounds that others have shared, but here is my idea for an easy solution:
My “Tableau Hack”
I used a dummy dataset to recreate and share my process and end result.
For example, let’s say we are looking at 5 different metrics across my company and select peer companies:
Employee Expenses
Total Expenses
Employee Exp / Total Expense Ratio
Total Orders
3rd Party Ranking
We have final/actual data for all metrics from years 2011 - 2017, but we also have:
2018 Projections for
Employee Expenses
Total Expenses
Employee Exp / Total Expense Ratio
2018 Actuals for
Total Orders
3rd Party Rankings
My Company 2019 Projections for All 5 Metrics
Let’s assume I already have my dynamic Metric measure, Line Graph, and general dashboard design built out. (For info on how to create dynamic dimension and measures using parameters check out Ryan Sleeper’s blog post on Evolytics)
Phase 1: Adjust shading on main graph
Go to your Line Graph sheet (the sheet you want to highlight projections on) and format the worksheet shading to None (transparent)
Phase 2: Highlight projected years with a colored container
We know the majority of our metrics have two years of projected data (2018 and 2019). Use a floating colored text container to easily distinguish those years:
Under the Dashboard menu, insert a floating text object. (No need to type any text)
Under the Layout menu choose color (I used the same green as my line graph, adjusting the Opacity to 12% so it would be much lighter)
Resize and align the Highlight Container with your line graph and cover appropriate years (here it’s 2018 and 2019)
Right click and under Floating Order click Send to Back
Updated view:
Phase 3: Create a dynamic cover sheet
Now for the metrics with only one year of projections (2019 data):
Create a new sheet (I renamed mine Dynamic Sheet)
Under the Marks Card:
Type " " (a space) at the bottom, hit enter
Make sure Square is selected in the dropdown menu
Set the Color to white (or the same color as your dashboard background)
Set the Size to the max on the scale
Turn off Tooltips
Format Worksheet Shading to None/Transparent
Create New Calculated Field:
• Name it “Dynamic Container”
• You want to highlight the two metrics that only have one year of projections and force the sheet to “appear” and cover up the other Highlight Container already on your dashboard
• Use a simple string calculation:
• Drag the Dynamic Container calculation to the Filters card and check “show” (be sure your parameter is set to one of the metrics in your formula or this option will not pop up)
Phase 4: Format the Dashboard
Go back to your dashboard and drag the Dynamic Sheet as Floating onto the page
Hide the Title
Right click, under Fit, click Entire View
Resize and align with the Line Graph and cover appropriate year (here it’s 2018)
Adjust floating order to be: Line Graph on Top, Dynamic Sheet in the Middle, and Highlight Container on Bottom
Add a quick projection legend for your viewers and ...BAM! Now when you change your parameter, the view will dynamically update.
You could also incorporate multiple Dynamic Cover sheets depending on how your projected data varies for each metric.