With Level of Detail Expressions, calculating year over year change is simple with the 7 steps.

A unit histogram is a chart that shows the distribution of data points across a series of bins of a measure.

Unlike a traditional histogram that uses bars, a unit histogram consists of a set of marks, each representing a member of a dimension. 

In this example, our final result is a unit histogram showing the distribution of states by their average sales.

If you like this, you'll love Next-Level Tableau. You get over 175 hours of live training with me, 130+ on-demand videos, workshops, office hours, workbooks, data sets, lots of new videos each year, any new courses I create, and a great Community.

Get more info and sign up today at nextleveltableau.com.

Want to cut the number of sheets you need in your KPI dashboard in half? These 11 simple steps will get much closer.

Steal the calcs to speed up your work.

P.S. If you like tips like this, remember to follow me on LinkedIn so you're notified about everyting I post. Content curated just for you, 3 times daily.

Set actions are super useful when you want to see how one thing compares to the rest. In this use case, I show you how you can click on a State and have all other States show as a comparison to the selected State.

In this tip, I show you how to use level of detail expressions to find the boundaries of a line and turn it into a square by finding the ratio of each point on the line to the width and the height.

Note: A couple of the calculations were backwards in the video, so download the workbook to ensure you have them correct.

I have been keeping a "master" workbook of many of the exercises I use to teach at The Data School, particularly when we get into advanced topics like table calculations, level of detail expressions, set actions, parameter actions, parameters, etc. I started this workbook a few cohorts ago, so surely there are tons more that I have forgotten. Tooltips aren't always formatted, cards are removed, and that's not the point. The calculations are the meat of this workbook.

I've decided to share these with you, the Community, so that you can learn right along with the DSers. As of this writing, there are 61 examples in the workbook. As I add more, I'll re-publish the workbook.

You are welcome to use this in any way you please. However, please cite me as a source if you do; that's only fair. I would recommend you try to rebuild these examples before looking at the solution; you'll learn way more that way. The examples at the start of the workbook tend to be simpler, so start there and progress your way through. These aren't necessarily "perfect" solutions (there isn't a perfect solution), but they solve the puzzle we were seeking to solve.

And the name? It is inspired by the Big Book of Dashboards. I've always loved the name of that book! This workbook should be a good companion to the Tableau Visual Vocabulary I created in July 2018.

Enjoy! Now go learn!

Four weeks to go with Makeover Monday 2019. We've had lots of interesting vizzes to makeover and lots of interesting data. This week, I wanted to pick a simple visualization and simple data.

What works well?

What could be improved?

What I did

This week we delve into some tricky calculations for Workout Wednesday week 39. My goal for this analysis was to understand:

1. How much the maximum selling product sub-category accounts for in a given month.
2. How much that contribution has changed throughout the year.

Requirements:

That Emma! She's quite the sneaky stinker! For Workout Wednesday Week 26, she asked us to build a trellis chart full of slope charts that compares election results by political party by constituency for 2015 vs. 2017.

Fortunately, I have my trellis chart calcs safely saved in my notes, so I didn't have to Google those. The tricky bit for me was the sorting. I'm not going to spoil how I did it for you, but you can download my workbook to see how I did it. As usual, Emma and I took very different approaches for the calculations required for the sorting. She used LODs for all of her calcs, I only used one. They both work though! It all depends on how your brain works I suppose.

The data prep parts were pretty straight forward (thank you Emma for alerting us to the need to do this). Emma loves little tricks in the formatting, but I didn't see any this week.

One thing I did different was to provide a "buffer" for the year labels. I place them 10% above the highest value so that they don't overlap the slope chart lines. Emma's year labels sometimes overlap with the slope chart lines. Just a personal preference for me.

Great fun Emma! Thanks! Took me about 90 minutes including this blog post on the train from Frankfurt to Hamburg. Great use of my time! #AlwaysLearning

Click on the image for the interactive version and to download.

I'm on a plane headed for an 8-day holiday with the family, so no video tip this week. I doubt my neighbors on the flight would appreciate me creating a video. I'm working on a series about LOD expressions to put them into an easy to understand sentence much like I did with table calculations (read that post here). For me, the easiest way to make sense of LODs is to go through lots and lots of examples.

This post will walk through a pretty simple, but very applicable use case. Let's say you've set goals at your company that you want to sell £500 worth of products to each customer each quarter. But you also need that goal to be flexible. That is, the user needs to be able to change that amount.

First, we need to create a simple view that has Year and Quarter on the Columns shelf. I'm going to use continuous quarters.


Next, we need to create parameter to allow the user to enter the minimum threshold. To do that, I'll right click on Sales and choose Create -> Parameter. This automatically creates the parameter type that I need. I'm chose the All option in the Allowable values section because I want my user to be able to type in any value. I also set the Display format to currency.


Right click on your parameter and show the parameter control. The parameter doesn't do anything until I use it in a calculation. What we want to do now is create a level of detail expression to count the number of customers that met the threshold in each quarter. First, though, we need to determine the sale for each customer in each quarter. In a plain English sentence this would be:

For each customer, year and quarter, calculate their total sales.

Since customer isn't in the view, that's our clue that we need to use a level of detail expression. The calculation looks like this:


Ok, now I need to determine how many customers met the threshold. To do this, I'm going to edit my calculation and add a simple IF statement.


This calc is looking at the sales for each customer in each quarter and if that customer meets the threshold, we count them. Otherwise, we don't count them. Dropping this into onto the Rows shelf, we now get a simple line chart.


As you change the parameter, every recalculates perfectly. Great! Now we know how many customers met the threshold each quarter. The problem now is that we don't have a lot of context. Is the 210 that met the £500 threshold in January good? Maybe we should have a secondary goal that X% of customers need to meet the target. In other words, 210 in January represents what percent of all customers?

To answer that we need to create another LOD expression to count the total customers.


One more calculation and we're nearly done. This time we want to calculate the % of customers that met the threshold.


Format this calculation to percentage (I like one decimal) and then replace the measure on the Rows shelf with this new calculation.

Great! Much more context now, but we can make it even better. Let's set an additional goal. How about we give our user the option to set the % of customers that need to meet the target as well? Let's start by creating a parameter to allow the user to enter the % of customers meeting the target.
Show the parameter control, add a reference line that uses the Target % value, change the Quarter field on the columns to discrete and change the mark type to bars. Make the bars as wide as they'll go and add a light grey border.

From here, I would create an additional calc that highlights the bars that are not meeting the threshold. Why? Because those are the areas of concern. The calculation looks like this:


Dropping that onto the color shelf and tweaking the colors, I now have a pretty complete view. The last thing I would do is adjust the format of my quarters and put it all together in a dashboard.

Hopefully you found this a good use case and explanation. It's also helped me realize how much easier it is to create videos.

Last week on my Data Viz Done Right site, I wrote about a distribution visualisation created by Harry Enten that shows the range of dates for snowfall at select U.S. cities. It's Super Bowl week, so I decided to recreated the style of Harry's viz in Tableau with the same NFL data that Emma used last week. Your challenge this week is to re-create my viz.

Below is the visualisation that I created. If you're reading this on a phone, tap on the image for the interactive version. Some requirements to keep in mind that are intentionally designed to make this tougher and to make you learn:

1. All of the elements must be floating on a dashboard sized 650x650.
2. You cannot use the Player dimension anywhere in the view.
3. Match my colors including the background
4. Create the legend (HINT: It's not an image)
5. Match the tooltip (Note the stats that are displayed in the tooltip. This will be a bit tricky. Essentially you need to count the number of players that are contained within each band.)
6. The viz should update based on the stat selected. The user should be able to choose between: Attempts, Completions, Interceptions, Touchdowns, and Yards
7. The title should update dynamically based on the stat the user selects.
8. Optional: Use Montserrat font (you can download it from Google fonts)

Nice challenge from Emma this week! She’s a massive NFL fan and since the Super Bowl is upon us, she decide to challenge us to create a common baseline chart that shows the passing yards for QBs in the NFL over the course of their careers. Go to her blog for the full challenge details.

First requirement was to filter to QBs that had played at least 3 seasons and had at least 2000 total passing yards. I did this by adding a data source filter. The benefit of doing this is that my Player list will now only include those that meet the criteria and I won’t need filters elsewhere.

Next, I created a LOD calc to get the first year for each QB.

I built upon that calculation with this calculation that gives me the number of seasons played per QB. This goes onto the Columns shelf.

The cumulative passing yards is merely a running total table calc set at the Year level. This goes on the Rows shelf.

I put Player on the detail shelf to get a line per QB. I also put Year and Yds on the Detail shelf since I need those for the tooltip.

Next was a parameter to pick a QB and use that to highlight the QB chosen. I then created a simple calculation that check the Player again the parameter and put that on the color shelf.

Last was the dot on the end of each line. To do that, I created a calculation that checks if it’s the end of the line and the player selected and, if so, return the cumulative passing yards. Since this is a nested table calc, it’s important to set both table calcs to compute using Year.

Some tidying up, adding the footnotes and I was done. I decided to float all elements on the dashboard to ensure they would render exactly as I wanted them to. Another fun week of learning something new! Thanks Emma!!

Really fun challenge from Emma Whyte for this week’s Workout Wednesday that helped me learn loads! I’ll highlight some of those below, but first, here’s the challenge:

1. The dashboard title should change to say 'All Departments' when you select 'All' in the filter
2. The department logo should change when you select a department, but disappear when you select 'All'
3. The line chart title should update when you change the filter
4. All the charts and tooltips should look the same as Emma's

And here’s my final version:

Here are some things that helped me along the way. First, when I need to add a dot onto the end of a line, I usually create a table calc or LOD calc and create a dual axis chart, but that’s totally unnecessary. I never knew that when you turn on mark labels you then have the option to show only the Most Recent. The great thing about this option is it make the mark a dot. Perfect for this exercise!

To get the dashboard title to update dynamically, I created a sheet instead of using the dashboard title. This gives me more flexibility to use calculations. I created this LOD calc to first count the number of categories in the view and then return a string based on that result.

I then placed this new field on the Detail shelf and updated the title of the sheet.

For the department logos, I knew I wanted to only show a shape when “All” is NOT selected, so I created a new sheet and then created this LOD calc that simply checks whether the number of product categories is 1.

I then placed the field on the Filter shelf and chose to Exclude False (keeping True only works too). From there, I placed the Product Category field on the Shape shelf and assigned the shapes for each Department.

To get the month into the title of the line chart I created this LOD calc to return the latest month.

I placed this field on the Detail shelf and changed it to continuous Month and updated the title. The title is also supposed to show sales for the most recent month. I did this with a table calculation.

That’s about it. The rest was formatting and dashboard layout. Emma floated some things in her dashboard whereas I chose to tile everything. Both work just fine.

Overall, I learned quite a bit and that’s what Workout Wednesday is all about!

For this week’s Tableau Tip Tuesday I show you how to use Level of Detail expressions to get the latest year and prior year and then compare then to create visual alerts.

Another Data School Gym challenge for you. Today Jonathan MacDonald reached out to me with a question. I’m glad he reached out because I’m constantly asking him question. He asked:

How can we create a timeline Pareto chart? That is, we need to calculate the cumulative sales for a dimension from it’s first sale until it’s most recent, but the time has to be expressed as a % of cumulative days.

So, here’s the challenge for you. Create this chart below. It’s based on Superstore Sales and there are no big tricks. Just see if you can do it. Some hints:

1. You will need an LOD calc for the x-axis.
   
2. Include a single select parameter for the dimensions. Each line on the chart represents one element of the selected dimension.
3. Include an option to highlight a specific element within the dimension selected.

I’ve disabled the download option on the workbook so you can’t cheat. Tweet me when you think you’ve figured it out.

---

For this week’s Tableau Tip Tuesday, I continue the series of Level of Detail Expressions by looking at the difference between average sales and average sales per customer. The idea here is you might want to answer the question:

For each state (or product category or whatever), what is the average sales per customer.

This is a very different question than simply the average sales per state. I walk through a couple of examples that hopefully help clarify the technique.