November 9, 2014

Tableau Tip: KPIs and Sparklines in the Same Worksheet

I'm writing this blog post outside of a Starbucks in the Sao Paulo airport. Sao Paulo you say? I'm in Brazil this week with four other folks from the San Francisco Bay Area TUG to help Tableau leaders in Rio de Janeiro and Sao Paulo get their own TUGs started. I see this as a way that I can give more than I take from the Tableau community. Yes, this is what I choose to do with my vacation time (it's a bit of a sickness) and no, Tableau doesn't pay me to do this.

Anyway, as I was on the plane, I thought it would be great to kick off the week with a new tip. Today, I'm writing about combining KPIs and sparklines in a single view. It's very common for business users to want to see KPIs and trends in the same view. These give them a sense for the overall direction of their product and also highlight the most meaningful numbers to them. I often see people create these as separate worksheets in Tableau, but with this post, I'm going to show you how to combine them into a single view.

Combining them into a single view provides a couple of benefits:

Tableau only needs to render a single sheet, so until parallel processing comes out in v9, you'll see a performance benefit.

If you have a hierarchy, then expanding the hierarchy will keep the table and the sparklines together.

This example is using a very simple data set of daily volume for several stocks. My KPIs include:

Trading volume for the last 7 days

Trading volume for the prior 7 days

Week over week change (raw & %)

This should be a fairly typical set of KPIs for most products. You could easily expand this technique to include m/m or y/y calculations depending on how your organization calculates those. Here's the final solution, with details on how to create this view below.

Step 6: Add Latest Week Volume, Prior Week Volume, Change and % Change to the Rows shelf and then convert them all to discrete. This will make them appear as columns of data. Reorder these discrete measures as you see fit.

Step 7: I like to include an indicator on the end of my sparklines (see this blog post). I do this by creating a calculated field to get the value for the latest date and then add it as the secondary axis. I then color it by the % change.

Step 8: Create a calculated field to filter to the last 30 days, add it to the filters shelf and choose True.

Step 9: Clean up and formatting. I removed the gridlines, removed the lines for the column divider pane, hid the header for Volume, changed the worksheet font size, right justified the discrete measures on the Rows shelf, narrowed the width of the sparklines, made the sparklines thinner, made the headers bold, and bottom aligned the headers.

Step 10: I created a dummy header for my sparklines that shows the "as of date" and added it to the Columns shelf.

Step 11: I created a parameter to let the user decide what they want to sort by. Creating a parameter requires a few steps of its own:

1) Create the parameter

2) Create a calculated field that returns the negative of the value chosen in the parameter. I add the negative for a sort descending effect.

3) Convert the field to discrete, add it as the first pill on the Rows shelf, then hide the header to hide the field.

This doesn't take very long once you do it a time or two. Give it a shot! Download the workbook here.

As for stealing, I have always told my colleagues and students that in the Tableau Community it is not about Copy and Steal Everything, but rather, Collaborate and Share Everything that makes the community so virtuous.

I have 5 rows of data coming from different tables/different extract and it is a MOM calculation. So near to each I have to display an arrow showing where it is up or down.Can anyone help me with this. Each row is not the same measure but different.

All the key performance indicators are from different extracts which comes from different tables. I used table calculations and found the MOM . Now I have to display an upper green arrow or lower red arrow near the MOM where I am not able to get a solution

Instead of last week, I want to display last month. How do I change the calculated field to last month. My date dimensions is shown as January 2014, February 2014, etc. through January 2015. Can you help? Thank you.

Researching more on spark lines via google, ran across this post, and wanted to help simplify one of the calcs that I had time to take a look at. --Only looked at this one calc in the blog. Again I rather enjoy the complexity added here, but it appears unnecessary given Tableau's flexibility with last(). Stepping away from 6 functions to give a single circle and increasing the performance of the viz/dashboard, which also gives you a quick and easy to support calc.

Last Updateif last()=0 then sum([Measure]) end

[Measure] can be a measure swap calculated field and complexity of sum/avg/max/etc can be added in that calculated field.

Since the numbers in the table are dimensions, you can't do that easily. There are a few very complicated workarounds. You should typically avoid red/green as well since they're not colour-blind friendly.

Thank you for this great post. What modification can you recommend, if I am trying to take a unique count of users in a given time period (eg - last week). I am trying to follow your guidelines to reproduce this on a project I am working on. My issue is that instead of summing up the volume as you have done, I am trying to take unique count of users for a given time period. Although I can calculate the unique users and throw it in the view, I am unable to reproduce in a manner which displays both the sparkline and the numerical value in one row.

Hey Andy, is there a way to show the same last week data for all Stocks if there is missing data for one of the stocks? And then to add a rank # column to the left of the "Stock" dimension column? What I mean to say is, let's say you did not have data for just Amazon from 1/26/14 and onwards. Using the existing code, you would end up showing the data for the week of 1/19/14-1/25/14 for Amazon instead of zero.

Thanks for the quick reply Andy! It's much appreciated. I'm still confused though--I had previously been looking into using INDEX() to create a rank column but have not had much success (the calculated field I created when placed on the rows shelf either ranks correctly but does not sort in sequence or ranks incorrectly but in sequence). Is there some way to combine the LOOKUP() function with INDEX() to create the rank column sorted by latest week volume?

I am new to tableau and am mesmerized your dashboard skills and Awesome work that you do to help us all with good dashboards that we can use in our day to day life.

I just tried to prepare a similar dashboard to show the stock price of stocks during a selected period....and by far I was successful and got what I got. May be would you be able to able to help ? How can I share the dashboard with you

Hi andy , I am a new tableau user but i feel lost in an ocean when learning tableau . Could you please write a post that provides some direction for new rookies to follow while learning tableau . Regards , Vishal .

The calculations in this post could easily be changed to use parameters instead. For example, in the Last Week calculation, you would change the 6 to the parameter instead. Rinse and repeat for the other calculations.

This is a really helpful guide. Here are the 2 challenges I'm facing:1 - I need to view the data based on a Sunday to Saturday weekly schedule. I.e., I can't use the max(date) as my reference point. Instead I built a function in its place to find the last Saturday in the data:

if datename('weekday',max([Date])) = 'Saturday' then max([Date])else if datename('weekday',max([Date])) = 'Monday' then date(max([Date]) - 2)else if datename('weekday',max([Date])) = 'Tuesday' then date(max([Date]) - 3)else if datename('weekday',max([Date])) = 'Wednesday' then date(max([Date]) - 4)else if datename('weekday',max([Date])) = 'Thursday' then date(max([Date]) - 5)else if datename('weekday',max([Date])) = 'Friday' then date(max([Date]) - 6)else if datename('weekday',max([Date])) = 'Sunday' then date(max([Date]) - 1)end end end end end end end

This seems to be working but would appreciate your feedback.

2) The remaining issue I have is that not all dimensions have a max(record) that falls under the same week. So for dimension A, my max(date) is 1/3 (a Sunday) and for dimension B, my max(date) is 1/9 (the following Saturday). So what I get then is 2 different weeks falling under my Last 30 day header and it also messes up my Last Day colored spark dot.

I replaced the calculation above with a parameter to set the Saturday date I was wanting and replaced all the references back to that parameter value, which appears to be working from what I am seeing. Do you see any issues with that solution?

Thanks Andy! I have one last question. I have a ton of dimensions and ideally I could just filter by the top 3 movers (week over week difference) for a dashboard. I'm running into issues with a Top N filter I assume because there are table calculations involved. Is there a good way to work around this?

The easiest way to get around it is to put the table calc as a dimension as the first column in your view (assuming the data goes down the table), then add an index table calc as have it filter to the first 3. There are probably simpler workarounds. Just search the forums and I bet you'll find something.

I'm trying to create something exactly like this post (e.g., summary of the overall KPI, and a sparkline over last 30 days), except where the example in this post has the %age Change value I'd like to display an Up or Down arrow depending on whether the change is positive or negative. I'm trying to do this all on one sheet, rather than creating a dashboard of multiple sheets.

Hi Andy, i have replicated the same for one of my assignment, on top of it i would like to see Top and Bottom X records, i have used unique-rank function and dropped it just right to sort field and left to stock, but then the view is splitting up differently for different computations & unable to get the exact row numbers. i have noted that this happen when the date field is in continuous exact day level only..