Examples for LOD expressions

Tableau's Level of Detail (LOD) expressions provide a way for authors to compute aggregations that are not at the level of detail of the visualization. Here you can find some examples.

Introduction

LOD expressions allow authors to customize where they want Tableau to stop its aggregations. Unlike table calculations, totals, or reference lines, LOD expressions are computed in the data source. LOD expressions can be dimensions or measures and are often denoted with the use of { } in the Tableau calculation.

• A general LOD expression where no keywords are used
• FIXED
• EXCLUDE
• INCLUDE

note

The examples in this document are based on the sample project available at the AtScale downloads site

For more information, please refer to Tableau's Top 15 LOD Expressions blog post.

No Keyword Measure based LOD: Percent of Total

You need to understand how each country has contributed to global sales based on our data set. The calculated Tableau LOD expression used in this case is:

{SUM([Sales Amount])}.

This LOD expression prompts Tableau to calculate the total sales amount for the entire data set it is connected to. The approach allows the author to filter on other dimensions, such as Customer Occupation, to help understand how each of the occupational areas has contributed by country to global sales.

Fixed Keyword Dimension based LOD: Cohort Analysis

You need to understand how your customers have contributed to the global sales over the years. The calculated Tableau LOD expression used in this case is:

DATE({FIXED [Customer_Key]: min([Order Day])}).

This LOD expression prompts Tableau to set the first order date of each customer in the data set as the date on which a shopper became a customer. The approach allows the author to show how longer-tenured customers have contributed to sales over the years.

Fixed Keyword Dimension based LOD: New Customer Acquisition

Leveraging the customer acquisition calculation from the "Cohort Analysis" scenario, you use the same LOD expression to understand your trends in New Customer Acquisitions. The calculated Tableau LOD expression used in this case is again:

{FIXED [Customer_Key]: min([Order Day])}.

This time, you will want to view a running total on the DC of [Customer Count] by the continuous DAY of [Order Day] based on only "New" customers.

To set up the visual:

• Bring in [Order Day] and set it to a continuous DAY

• Bring in the [Customer Count].

  Notice the automatically assigned function call of CNTD(). This is an Atscale DC (Distinct Count), so Tableau automatically assigns CNTD(). Add the Running Total table calculation to this measure.

• Change the Y-axis to use a logarithmic scale to generate the curved line chart

• Filter on "New" for the [New or Existing Customer] dimension

• Bring in [Country] into the color code mark

Fixed Keyword Dimension based LOD: Customer Order Frequency

You need to understand the number of customers who made 1,2,3,...,N orders. This requires a count of customers by the total number of order groups. The calculated Tableau LOD expression used in this case is:

{ FIXED [Customer_Key] : COUNTD([Total Sales Orders]) }

Here, [Total Sales Orders] is a DCE (Distinct Count Estimate) of Customers in the data set. This LOD expression prompts Tableau to calculate a distinct count of sales orders by customer. This will give the number of orders made by each customer. You can then use the LOD calculation into a dimension to provide the view of the number of customers who have purchased 1,2,3,...,N orders.

To set up the visual:

• Bring in [Order Day] and set it to a continuous DAY

• Bring in the [Number of orders per customer LOD] LOD calculation into the worksheet, and change the measure into discrete dimension.

  

• [Customer Count] is a DCE (Distinct Count Estimate) measure from Atscale.

  When bringing in this measure, you will notice that Tableau automatically chose the CNTD() since it recognizes this Atscale measure as a DCE.

• To get a total count of customers for each total order number, you will need to override the pre-assigned CNTD() function with SUM().

  

The final visual will look like the following:

EXCLUDE Keyword Measure based LOD: Comparative Sales Analysis

You need to understand how the rest of the product lines have performed against a particular product line. The calculated Tableau LOD expression used in this case is:

{ EXCLUDE [Product Line] : SUM([Selected Sales Amount]) }.

This LOD expression prompts Tableau to set a custom aggregation on a target product line's sales total to use as a comparison to other product lines' sales totals. The approach allows the author to show the difference in sales between a target product line and the company's other product lines.

INCLUDE Keyword Measure based LOD: Largest Average Customer Purchase

You need to understand the largest sale made by customers. The calculated Tableau LOD expression used in this case is:

{INCLUDE [Customer_Key]: MAX([Sales Amount])}.

This LOD expression prompts Tableau to find and set the max sales amount per customer. This approach allows the author to show an average of the largest sales by customers for each country sorted in descending order based on the LOD calculation.

In addition to showing the largest average customer purchase, the author can also allow the user to choose to view by a parameterized Top N states that have the average largest customer purchase which is the LOD calculation.