top of page

Blog

Search

Dynamic Data Projections Highlights

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)

Note: everything is in a tiled container except the Line Graph sheet which is floating

Phase 1: Adjust shading on main graph

Go to your Line Graph sheet and format the worksheet shading to None (transparent)
  • 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.

67 views

Recent Posts

See All

Comments


bottom of page