# Top 15 LOD Expressions

April 6, 2015

Tableau’s mission is to help people see and understand data. Our features are carefully designed to help people transform data into meaning. One area of focus is calculations. The easier it is to express ideas in a calculation language, the more meaning people can generate. The introduction of Level of Detail Expressions in Tableau 9.0 is a breakthrough in this regard. These new expressions let people express powerful concepts using simple statements.

Level of Detail Expressions provide a way to easily compute aggregations that are not at the level of detail of the visualization. You can then integrate those values within visualizations in arbitrary ways. That may sound abstruse, so this post will illustrate the concept through a series of common questions. Near the end, we will dive into some more advanced analytic examples. The whitepaper on Level of Detail Expressions provides a more general overview. For details on syntax and usage, see Level of Detail Expressions in the Tableau Desktop online help section.

Each of the following 15 workbooks contains customized data sources and can be downloaded for further details. If you do not have a copy of Tableau Desktop, you can get a free 14-day trial here. We recommend that you use these customized data sources if you choose to follow the instructions and re-create the views.

### 1. Customer Order Frequency

Finding the number of orders each customer has made is relatively easy, but what if we wanted to know the number of customers who made one order, two orders, three orders, and so forth? To build this view, we must break up the number of customers by the number of orders made. This is a simple question, but breaking out a measure by another measure would be difficult without Level of Detail Expressions.

Consider the sales database of a superstore that has multiple items per order. The distinct count of orders by customer gives the number of orders each customer made. A simple LOD expression can turn the number of orders into a dimension that breaks out the number of customers.

### 2. Cohort Analysis

Do longer tenured customers make a larger contribution to sales? The view below groups customers by the year of their first purchase to compare sales contributions annually across cohorts. The minimum order date per customer will give the first purchase date. However, since the data in the view is not displayed by customer, we must use an LOD Expression to fix the minimum order date per customer.

### 3. Daily Profit KPI

We can certainly view profit trends over time, but what if we measure our success by the total profit per business day? We would probably want to know the number of profitable days achieved each month or year, especially if we were curious about seasonal effects. The following view shows how LOD Expressions allow us to easily create bins on aggregated data such as profit per day, while the underlying data is recorded at a transactional level.

### 4. Percent of Total

What is each country’s revenue contribution to global sales? If we color by the contribution percentage, we immediately see that the US has the highest contribution to the global sales revenue. However, we may want to focus on a market like the EU that, in absolute terms, has a small contribution. Without LOD Expressions, filtering on a market would cause the percent of total to recalculate, displaying each country’s contribution to its market. With a simple LOD Expression, we can filter on a market, and still measure the global contribution.

### 5. New Customer Acquisition

What is the daily trend of total customer acquisition by market? Finding the trend in this data will help us understand how well the regional marketing and sales organizations are doing at generating new business. The steeper the line, the better the acquisition trend. As a line flattens out, some action must be taken to increase lead flow.

An LOD Expression ensures that repeat customers are not miscounted as new customers, as data must be evaluated at the customer level even though it is displayed visually by market and day.

### 6. Comparative Sales Analysis

It’s relatively straightforward to find the difference from average, but what if you wanted to find the difference from a selected category? First, you must isolate the sales of the selected category. Then an EXCLUDE Expression is needed to repeat that value across all other categories. It is then easy to take the difference of each category’s sales from the rest.

### 7. Average of Top Deals by Sales Rep

What is the largest deal that each sales rep has closed? And then considering these top deals by sales rep, what is the average by country? An LOD Expression allows us to look down to the sales rep level of detail even though the data is displayed visually at the country level. In the view below, the average top deal size by sales rep is higher in countries colored blue and lower in countries colored orange. We can use this information to guide drill-down analysis from country to sales rep.

### 8. Actual vs Target

In this visualization, we are displaying the difference in actual profit compared to target profit per state for a chain of coffee houses. In the top view, we can clearly see which states have exceeded the target and which states missed the target. However, when aggregating up this way, we can miss important nuances. Some states are above target because every product sold in that state is above target. Others are above target because a single product exceeded its target by enough to make up for all of the other products that missed their target. We can use an LOD Expression to identify the percentage of products sold within a state that are above target.

### 9. Value on the Last Day of a Period

Data that represent the status on a particular day such as inventory numbers, headcount of employees, or daily close value of a stock need be treated differently than metrics that can be aggregated such as sales or profit. In these cases, you may want to display the value on the last calendar day of a month. Furthermore, we would expect that drilling down from month to week would update to display the value on the last day of the week. In the example below, we have stock data for multiple tickers on a daily level. The view compares the average daily close value to the close value on the last day of the period. Using a simple LOD Expression, we can look down to the daily level, even if the data is displayed visually at a higher level.

The following 6 examples illustrate how level of detail expressions can be applied to more advanced scenarios, as well as use cases that draw on the broader feature set of tableau. For further examples of more basic scenarios, watch the On Demand Training Videos on LOD expressions.

### 10. Return Purchase by Cohort

Acquiring new customers can be expensive, so we want to ensure that existing customers are making repeat purchases. How many customers take one, two, three, N quarters to make a repeat purchase? How many have never made a repeat purchase? What does this behavior look like broken out by quarterly cohorts? We can use a FIXED Expression to find the first and second purchase dates per customer, and derive the number of quarters to make a repeat purchase from this.

### 11. Percent Difference from Average across a Range

Example 6 shows how to make a comparison to a single selected item, but what if you wanted to make a comparison across a range of values? For example you may want to compare the daily close value of a stock to the average daily close value before a major event occurred that affected the industry in question.

### 12. Relative Period Filtering

A common metric for analyzing performance is year-to-date and month-to-date comparisons relative to the previous year. We can do this easily by filtering relative to today, but what happens if the data is refreshed on a weekly basis? Suppose your last refresh occurred on March 1, but the current day is March 7. A month-to-date comparison would show March 1 through March 7 of the previous year versus March 1 of the current year. This could cause significant alarm, where none is needed! A simple LOD Expression allows us to find the maximum date in the dataset.

How many users log in to a website or application once a month, once every two months, once every three months, and so forth? What is the average login rate? How does the distribution skew around this average? The granularity of the data is log in date per user ID. In other words, there is one row for every day that a user logged in. Building this view requires slicing the number of customers by the login rate, meaning that we must slice a measure by a measure. We saw in example 1 how LOD Expressions make this type of analysis easy.

### 14. Proportional Brushing

The most fundamental question in any analysis is: “Compared to what?” Sometimes when filtering, we would like to compare the selection to the total amount, as opposed to simply filtering down to the selection. This technique is known as proportional brushing.

### 15. Annual Purchase Frequency by Customer Cohort

Are more tenured customers more loyal, where tenure is measured by the year of customer acquisition and loyalty is measured by annual purchase frequency?

We know from example 1 how many customers purchase exactly one time, exactly two times, and so forth. However rarely will a marketer want to identify all customers who purchased exactly five times. Instead, it may be more useful to know how many customers purchased at least five times.

Additionally we know from example 2 that most customers are acquired in 2011 and fewest customers are acquired in 2014. Looking at the absolute number of customers will only reveal this same trend again. Therefore, it may be more interesting to see the percent of total customers per cohort as a measure of loyalty.

So to rephrase the initial question more concretely, what percentage of customers in each cohort purchased at least one, two, three, N times in a year?

This example combines a variation of the number of orders LOD Expression from example 1, the cohort expression from example 2, and a variation of the percent of total expression in example 4.

Submitted by John Quinlan (not verified) on

Brilliant article, cannot wait to get my hands on this!

Submitted by Syed (not verified) on

The more I learn the more I fall in love with TABLEAU... :-) <<<<<< This comment was blocked and unpublished because Project Honeypot indicates it came from a suspicious IP address.

Submitted by HD (not verified) on

1 und 13, gelöste Herausforderungen, die ich ausprobieren werde. Vielen Dank für diesen tollen Artikel! Thumbs up!

Submitted by Moto (not verified) on

Nice article to get started !

Submitted by jigs (not verified) on

This is awesome. Hats off to the tableau team

Submitted by Craig P (not verified) on

This can all be done in Excel too.

Submitted by Ryan Szeliga (not verified) on

Terrific article. I'm wondering if you can show an example similar to #10, but instead of doing 'Quarters Between First and Second Orders', if you can simply show 'Quarters Between Last Purchase'.

Basically, what I'm trying to show is for a given customer, with an order in Q2 2015, whether or not that same customer had an order in the previous 4 quarters--and calculating this on an aggregate for all customers with orders in Q2 2015.

Submitted by Raghavendra K. on

love it

Submitted by Emmanuel (not verified) on

nice

Submitted by Ron B (not verified) on

seriously? Big difference in the amount of effort in order to produce in Excel vs. Tableau plus the added drill down and analysis capability an Excel file would be lacking. Have fun with that spreadsheet though.

Submitted by Traci D (not verified) on

GAME CHANGER!! I think my jaw was on the floor as I read through this article! This truly answers almost all of my challenges in marketing analysis. Thank you, Bethany, for such a fantastic explanation and real life examples. Thank you, Tableau, for truly revolutionizing data analysis.

Submitted by Sam L. on

Great article.

Small item, in 6. Comparative Sales Analysis, the tooltip for the marks on the right-hand side of the dual axis view is misleading.

Instead of its being "The difference in sales between and is ", as it is for the left-hand side, I believe it should just be something like "The sum of sales for is " since those uncolored bars show the total sales for each category.

Submitted by Sam L. on

Second paragraph should read as follows, apparently brackets are not sanitized in the text of posted comments.

Instead of its being "The difference in sales between Category and Parameters.Category is AGG(Difference From Selected)", as it is for the left-hand side, I believe it should just be something like "The sum of sales for Category is SUM(Sales)" since those uncolored bars show the total sales for each category.

Submitted by Susan D. on

Thank You!!!!!

Submitted by Raymond R. on

Very nice, Bethany. Fantastic examples!

Submitted by Yvain G. on

This is really useful, thanks guys !

Submitted by Steve A. on

Bethany, your awesomeness shows no limits ;)

Any chance you could put up a specific example comparing balance sheet and P&L KPI's on a time series? That would save me a lot of time :)

Submitted by Nicole E. on

These are fantastic, I can see a use for all of them.

Quick correction - in How To 2, for #15, it is actually Right click on Order Date, and select "Transform".

Submitted by arvind (not verified) on

My favorite part of this entire article was this comment by Craig P: "This can all be done in Excel too." I was almost in tears (with laughter) when I read that. Thanks for making my day :-)!

Submitted by Donne M. on

Hmm, pivot table/chart connected to a tabular model using a measure as a dimension should do it.

Submitted by Dan Murray (not verified) on

Excellent introductory material!

Submitted by Todd S. on

I am frequently asked about LOD expression use cases and these are very good examples -- thank you for posting!

Submitted by Chandra Bhatt (not verified) on

can't see the visuals on this page. they showed up when I first loaded this page but can't see afterwards. tried on Chrome, IE and Firefox to see if its browser problem but didn't work.

Submitted by Cliff B. on

Thank you so much for providing additional practical LOD information!

Submitted by Jim F (not verified) on

I second this, I cannot see the visuals, is there a special setting I need to set in my browser, or a recommended browser.

Same problem across Chrome, IE, and Firefox.

Submitted by Will R. on

Great real world examples.

Submitted by Leon L. on

Question for No. 6.

I think you are using {Exclude [Category] : SUM(Selected Sales)},
should it be {Exclude [Category] : MIN(Select Sales)} instead?

Leon

Submitted by Leon L. on

Sorry, my mistake.

{Exclude [Category] : SUM(Selected Sales)} is correct.
However when you calculate the difference, you should use Min([Sales of Selected Category]) right?

Leon

Submitted by Francois G. on

Great examples
Thanks

Submitted by Joshua (not verified) on

I laughed out loud when I reached the portion of this document in which irrelevant graphs were used insinuate that somehow being profitable less than half of each month is something besides a NET LOSS OF PROFIT! I then stopped reading, and wrote this comment. Thanks for the smile.

Submitted by Andrew K. on

how can I create a distribution of total sales history of my customers?

Submitted by lalitha (not verified) on

Submitted by Rune H. on

What does each bar means?
percent% of user login n times in one month? or login once every n month?
I think it should be "login once every n month".
Am I wrong or ...?

Submitted by Bill L. on

Which example does this? I see nothing that resembles your accusation. I think you missed the entire point.

Submitted by Rune (not verified) on

Figure Number 13
I can't understand why the mark said "24.91% of users login 5 times per month", but the reference line write "24.91% of users log in once every 5 months..."

Submitted by David R. on

Submitted by Yihua F. on

How did you edit the Question 11 tooltip?
I tried . but it did not work

Submitted by vinay (not verified) on

i want to display least measure from selected quarter
plz any help regarding this
im filtering quarter wise for each quarter only least value must display renaming value should be hide

Submitted by Jaksuthep T. on

This is a great article!

Submitted by Jaksuthep T. on

This is a great article!

Submitted by David Rogers (not verified) on

OK

Submitted by Roger (not verified) on

Its relatively complex to understand all at once...

Submitted by JUDE X. on

Great Article!!! Thanks Bethany.. Most of my doubts around LOD calculations are answered via your examples.. Thanks for taking time in preparing and sharing this cool stuff..

Submitted by Buck (not verified) on

Could you place a link to the data file you're using? The one include in the Tableau version I downloaded was different.

Submitted by Justin L. on

In #15, how do you get the tooltip to be dynamic? "In 2014, 10% of customers from the 2011 cohort purchased at least 2 time(s)"

Is there an article somewhere that explains how to do this?

Submitted by Bethany (not verified) on

Hi Joshua - that is exactly the point of level of detail expressions. They expose things in the data that can be lost in high level aggregations. Let's say that you lose \$10 a day for 20 days of the month, but you gain \$30 a day 10 days in the month. Overall, you made a profit of \$100, so you might think you've done well. But actually, you were unprofitable 66% of the days in the month, so despite being profitable, something went wrong. This effect is known as Simpson's paradox. The UC Berkeley gender bias example is quite famous.

Submitted by Bethany (not verified) on

Hi Justin,

If you download the workbook, double click on the tooltip on the marks card, you will see how it's done. It's very simple - just change the default tooltip to be a sentence. The information lab's data school recently published a good blog post as well. We can't post links here but if you search for "jazzing up your tooltips in tableau using calculated fields" you should find it!

Submitted by David M. on

First of all, Bethany, thank you for this great post! It's extremely educational and the best way to learn about LOD expressions.
I have a question about 6. Comparative Sales Analysis. It seems the same result can be achieved with just one added parameter Category and without the three calculated fields. Turn Sum(sales) into a table calculation difference from and difference along Category and difference from Category. Am I missing something? I could post the workbook, except I don't see any place here to do it.

Submitted by David M. on

There is something wrong with Russia!
In 7. Average of Top Deals by Sales Rep
If Russia is selected the average on the map is 3517, but the average in the bar graph is 3453.
It's only happening with Russia, with all other countries the two averages are the same.
What's interesting is that if I exclude a country, any country (except for Russia of course) then the map average is 3453.
Another interesting fact is that if I display countries as a list and one country is excluded I get 3453 for Russia, but if all counties are included then it looks like 3517 on top of 3453.