Reader Brian Bieber (no relation to Justin), a Data Analyst at Vanguard, sent me this question:
I'm currently running some monthly data where a list of records is assigned a KPI indicator like G/Y/R. I've been asked to produce a follow-up piece that would show "movers" from the prior month's data, just in a simple text view. So I guess what I need to try and do is figure out how to do a lookback/compare from prior month data to see who went from Y/R to G on the positive change side and who went from G to Y/R on the negative side.
What Brian didn't realize was that answer to his question was pretty much in the question itself; he needs to use to LOOKUP() function. I sent Brian a solution, but decided to fancy it up a bit more and add some more functionality using parameters:
The steps for building a KPI movers viz like this are pretty straight forward. I'm using the Superstore Sales data set that comes with Tableau in this example.
Step 1: On a PC, right-click drag Order Date to the Columns shelf. On a Mac, Option+Drag Order Date. Choose the continuous month option.
Step 2: Right-click on the Order Date pill on the Columns shelf and change it to Discrete.
Step 3: Right-click on the Order Date field on the Column shelf and choose Show Missing Values. This is important because it turns on data densification. Months where data does not exist get displayed.
Step 4: Add the dimensions you would like to slice the data by to the Rows shelf. For this example, I've used Region and Order Priority.
Step 5: Create a calculated field called Last and change the Default Table Calculation to Order Date.
Step 6: Right-click on the Last field you just create in the Measures area of the Data window and choose Convert to Discrete.
Step 7: Drag the Last field to the Filters shelf and choose 0. This will change the view to only show the latest date.
Step 8: Create a calculated field to get the value for the most recent month. Notice that I'm using the LOOKUP function. Change the Default Table Calculation to Order Date.
Step 9: Create a calculated field to get the value for the previous month. The only difference here is we need to offset the LOOKUP by 1. Change the Default Table Calculation to Order Date.
Step 10: Create two parameters: one to determine the lower limit of the top KPI (Great Performers) and one to determine the lower limit of the middle KPI (Average Performers).
Step 11: Create the KPI calculations for the most recent month and the previous month. Notice that I'm using the parameters.
Step 12: It's up to you if you want to follow this step. For this specific example, I wanted to see the KPI for this month and last month in the table, so I've added them to the Rows shelf.
Step 13: Create a calculated field that returns a boolean to compare the values for this month to determine if they are a "Mover". I'm using Brian's definition for this example.
Step 14: Drag the Movers field to the Color shelf. I chose to set the colors to a light gray when True and red when False. This way only the Movers stand out.
Step 15: Optional - Change the Marks type to Shape and drag Movers to the Shape shelf as well, then pick shapes that give the affect your looking for. I chose diamonds for the Movers and circles for everything else.
Lastly I did a few cleanup items before placing the worksheet on the dashboard you see at the top of this post:
Moved Order Date to the Detail shelf
Reduced the level of the row divider
Removed the bold text from Region
Added a calculated field to the Filters shelf that checks that the value in the Great Performers parameter is larger than the value in the Average Performers parameter.
This method could easily be changed to accommodate many different scenarios like day over day, week over week or year over year.
Layout containers are super useful, if you understand the concept for how to use them. In this livestream, I build a simple KPI dashboard of social media metrics.
I started by building a wireframe to show how I would use each container.
From there, I started constructing the dashboard. If you want to follow along, you can download the data here.
The link to the video below by Curtis Harris was a game changer for me as to how I understood Tiled vs. Floating. I can't recommend it enough. It's a big influence on every dashboard I create.
Two weeks to go in one of the best projects I’ve ever been a part of. In fact, Andy Cotgreave and I are hosting a Twitter chat on Monday at 4pm GMT. Get all of the details here and join us in discussing the impact this amazing project has had on all of us.
For week 51, we’re looking at the Washington DC Metro Scorecard. This is basically a KPI dashboard of quality and safety for the Metro. Let’s have a look at their scorecard:
What works well?
Nearly laid out and organized
Thumbs up and down give the status at a glance
Despite the rings, it’s visually appealing and draws you in
What could be improved?
While the rings make the comparison to goal easy, they wouldn’t work if something has to go past a full circle of the ring.
Inconsistent colors
No sense for performance over time
Can’t see all of the metrics in a single view; scrolling is generally a problem with KPI dashboards
You can’t tell how far each metric is from the target
They didn’t make all of the data available for us to use in the makeover. For example, there is no data available for the entire people and assets section. So what you’ll see this week from everyone will be nine of the metrics. For my makeover, I wanted to create small multiples of each metric over time. I liked their blue and red colors, so I used similar colors. I also wanted this to look more like a newspaper, so I went with the American Typewriter font. Lastly, if you look at this on a phone, you’ll see it a long, scrolling dashboard. I really like how this turned out.
In this session, I worked on Back to Viz Basics (#B2VB) week 4 and designed as many KPIs as I could in an hour. The video and workbook are below the resources and links.
In this week's tip, I show you how to create a daily KPI view that counts the number of profitable days per month. This is a video that demonstrates my take on example 3 from the Top 15 LOD Expressions blog post on Tableau's website.
On his great Evolytics blog, Ryan Sleeper, former Iron Viz Champ and renowned design guru, wrote about how to create donut charts in Tableau. His technique is sound. The concern I have is that he recommends using floating objects on a dashboard to create the hole in the donut.
If you have any experience with floating objects in Tableau, you know the pain of taking lots of time to make a dashboard look perfect in Desktop with floating objects, only to have it get all messed up once you publish it to Server or Public. I rarely recommend using floating objects for just this reason; it's frustrating and can make your work look sloppy. Hopefully Tableau soon fixes this bug that has been around as long as floating objects have existed.
Another reason to avoid floating objects is that there are more objects that have to be drawn on the dashboard, which can potentially have a performance impact.
While I typically hate pie charts, I think that donut charts, used in this specific way, can indeed be effective. As Ryan says in his post:
When used for the specific purpose of showing a metric’s progress to goal, with one “slice” being the current state of the KPI and one “slice” being the remainder to goal, I think a donut chart works well.
Here's how I would create a donut chart in Tableau using a single worksheet. The final product looks like this:
Having looked at the original visualization this week (here), it was pretty clear to me that the creators desired to create a KPI style dashboard. In a way it's good that I picked up on it that way. However, it wasn't designed particularly uclarly well to communicate most efficiently.
I decided during Watch Me Viz that I would stick with their idea of a KPI dashboard, but design it in a way that I thought would be easier to understand.
As per normal, it took way too long to faff about with the formatting. I made a couple of small changes after I finished the live stream. Click on the image below the video to check it out. Leave a comment on the video on YouTube if you have any questions. (Comments aren't appearing on the blog for some reason).
Week 29 had me all over the place. The topic of “my wife” was so broad that I had a bad case of analysis paralysis. Should I track her movements? Should I ask her to track something? Should I analyze all of the wonderful pictures she’s taken? Should I do a word analysis of her blog?
I then had a chat with my boss, Tom Brown, and in his Tom Brown way, he said that I should simply track the number of wives I’ve had over time. Well, I went with that idea and added a twist. At our #DATA15 presentation, I talked about the wife KPI, so what I decided to do is create a chart of the wife KPI since Beth and I first met in 1992.
From there I added major milestones to the dataset, categorised those event as positive or negative, then built a simple line chart. It's really interesting to me to see the ebbs and flows of our relationship. I would have never noticed it if I didn't see it depicted on a graph this way.
Note that on this postcard, I intentionally used red/green to indicate bad/good. I preach to people not to do this if they don’t know if their audience is colour-blind. In this case, my audience of 2 (Jeff and my wife) are not colour-blind, so I knew it would work.
In this second live stream demonstration of how to effectively use layout containers, I got into an ever so slightly more complex use case, particularly with the header section and how to use shading and padding. Check out the first video and dashboard about social media KPIs here.
Many of the examples of drill down you've probably seen so far use either set actions or parameter actions to create an additional column. The drawback is that you end up with an extra column that only has values when you drill down.
In this example, I show you how to use only ONE column to mimic drill down / drill up functionality. In total, it requires 2 parameters, 4 calculate fields, and 2 parameter actions. I find this method much more intuitive to the user.
Let me know what you think or how you put it into practice. Enjoy!
KPIs are measurable values that indicate how well a business is performing in achieving its key objectives and goals. They provide a clear and concise snapshot of critical areas within a business.
KPIs help to:
Monitor progress
Identify areas of improvement
Make data-driven decisions
In order to do this, KPIs must be tailored to specific goals and strategies.
Making KPIs Actionable
Here are 8 suggestions to ensure that KPIs are actionable and impactful.
Align with business objectives
Focus on relevance and simplicity
Set measurable targets
Collect accurate and timely data
Visualize your KPIs
Regularly review and update
Add context
Drill down
Actionable KPIs are vital for measuring performance and achieving results. You can drive your business forward by aligning data with goals and simplifying and visualizing KPIs effectively
Read more about KPIs in my newsletter here. Sign up so you don't miss any future newsletters at andykriebel.com.
⏬ DOWNLOAD the data, and starter and solution workbooks here. I would appreciate your support to continue making videos for you.
Containers...you either love them or hate them. And I want you to learn to love them.
In part one of this series, I showed you how to build a simple KPI dashboard with three cards. In the video below, I show you how to build a more complex dashboard that requires 7 containers:
Click for the interactive version
This video shows you how to organize the containers, how to use padding, and how to build an engaging dashboard for your stakeholders. Enjoy the video!
This week at The Data School, I was teaching DS24 about dashboard best practices and how to effectively use containers. We picked a visualization from the wild (this one by Ryan Sleeper) as an example for us to rebuild and to learn about effective use of containers.
If you'd like to learn more, here are two videos I produced about mastering containers:
To see how we build the example below, feel free to download it and pick it apart. However, if you really want to learn how to build something like this, try to rebuild it based on the image alone. The videos above will definitely help.
It's TC19 week and Eva has provided a data set about literacy rates around the World for the 1200+ people in Vegas to viz live plus the hundreds more of you that aren't live with us.
Here's the original viz:
What works well?
The data by region is ordered alphabetically, making it easy to find each region.
The bar chart is sorted by largest to smallest.
Nice filtering options
What could be improved?
A diverging color palette should only be used when there is a logical midpoint or goal. I don't see those in this viz.
The squares are hard to understand.
I don't find the map very useful. It would be more useful if it zoomed in when a region is selected.
There's no title.
There's too much text.
The bar chart seems to go out past the edge, or at least visually it appears that way.
What I did
I created a KPI scorecard so that I could understand the patterns for the overall or an individual country. Are literacy rates improving or regressing?
Show the distribution of the rates of the countries within each region
Within each region, which countries are above or below the median for that region?
How has the literacy rate changed over time?
Allow simple filtering options.
I drew inspiration from Workout Wednesday week 51 2018: Container Fun from Rody Zakovich. I love finding reasons to practice techniques I've tried before and want to master. Consider challenging yourself to learn something new each week.
Data HAS to be consumed quickly to be understood and to make quick decisions. A "typical" profit and loss statement is on a spreadsheet. However, spreadsheets are notorious for (1) errors and (2) being very difficult to spot areas of concern.
That's where dashboards come into play. Back in July 2017 I proposed a new way to visualize a Profit & Loss Statement (P&L). The dashboard has been very well received and put into practice at many organizations. They've reported back to me how useful it has been.
But then I got thinking about an even easier way to visualize the data. I felt like the previous version was a bit chart heavy. So, while I've been creating templates for other areas of a business (HR, Social Media, Customer Service), I decided to also focus on a new version of a P&L statement with KPIs. This dashboard is based on a Finance Dashboard example from Excel Dashboard School.
You can download the data I used to build this template here. Click on the image below to interact with and download the workbook.
The steps for building a KPI movers viz like this are pretty straight forward. I'm using the Superstore Sales data set that comes with Tableau in this example.
