Data Viz Done Right

April 4, 2017

Tableau Tip Tuesday: Using a LOD Expression to Count Customers Meeting a Sales Threshold

7 comments
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.

7 comments :

  1. Nice walk-through and use case - thanks! I am not sure I understand why the customer count is not fixed to the quarter like the sales...
    I.e. Customers = {fixed DATETRUNC('quarter', [Order Date]): COUNTD([Customer Name])}

    ReplyDelete
    Replies
    1. Hi Ulrik. If I include quarter, then that won't include ALL customers. I wanted the use case to be selling into all customers that we've ever sold into. Including quarter would answer a different question: What % of Customers that we sold into each quarter did we sell X amount to?

      Delete
    2. Ah OK - I was thinking of the other scenario. Thanks for clarifying!

      Delete
  2. In the first section, you gave me an inspiration to solve an issue I've been struggling against in my own visualization for weeks! Thank you!

    I had been filtering on a parameter then displaying a line graph of the sum of a metric. The issue was that some of the parameters led to missing datapoints in the line graph. Creating a calculated field with an if condition on the parameter and an else 0 when the parameter was not met allowed me to accurately display the graph! It seems so simple in hindsight, but the solution had evaded me until I read this blog post.

    ReplyDelete
    Replies
    1. Excellent! Glad I was able to help you Katie!

      Delete
  3. Hi Andy..thanks for this because for as long as i have been using Tableau, i still struggle with LODs. My current struggle is counting the number of items one seller has within timeline sets or buckets.
    So i created the timeline buckets and when i bring in the countd for the items then it looks fine, but i want to roll them up to be full numbers...but it is not working for me. The bucket is saying it is converted to a dimension but only color as it is still showing as an AGG. How would i get around this and use LODs. Thanks, Greg

    ReplyDelete
    Replies
    1. Hi Greg. What are you trying to roll up?

      Delete