4. EXCLUDE Expressions Part 1

Overview

The EXCLUDE expression is used to access data that is more aggregated than the data shown in the view. A common use-case for this expression is when colour coding complex bar-charts.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

Creating the LOD EXCLUDE Expression.

- EXCLUDE enables users to move to a higher level of granularity
- In this example, we create an LOD expression to exclude region in the view
- To do this, we create a calculated field called RevExRegion
- The LOD expression for this field is: {EXCLUDE [Region]: SUM([Revenue])}

Transcript

- [Instructor] In this lesson we're going to learn how to use the exclude LOD expression. Exclude is used for removing dimensions from our view, unlocking a lower level of granularity. Why might you ask would we remove a dimension from a view? Well, normally it's because we have two dimensions present and we only want to include one dimension in our calculation.

In this lesson, we're going to use the exclude expression to create the following visualization. This chart helps us examine which regions contribute the most revenue for each month of the year and as you can see by the variants, there's quite a bit of seasonality for the different regions that our sales people cover. While this chart might look pretty easy to build, there are a couple of complications that require the use of an LOD expression to get it just right. First of all, the metric we are calculating is the percentage of total revenue for the month in question although we are charting by region.

And second, the color scale is based on the total revenue for the month as well. So, how do we build this? Well, let's go to a blank workbook and find out. So, let's start by putting region in the Columns shelf. I'll also put Revenue in the Columns shelf and I'll take Payment Date and put this in the Rows shelf and for Payment Date I'll simply select Month and I'll drag the chart down to the bottom so that it's easier to read.

Let's start by getting our color coding correct. If I drag revenue onto the Color Mark, unfortunately this only color codes revenue within each region. So, for example, the Southern region in May has the highest value when in actual fact I want to color code by month excluding individual regions. And the current answer is simply too granular. So, instead I'll use an LOD expression.

This time round I'm going to create a calculated field to put my LDO expression in and I'll call my LOD expression Rev, exclude, ExRegion and in here I'll open my curly bracket, type exclude, then I'll type Region, colon and sum of revenue.

Then I'll close the curly bracket.

My statement is valid and complete. So, I'll press OK and remove my current color coding and instead add RevExRegion.

And this now works exactly as I want it to where we color code by each individual month regardless of region and so, my top value for the month of May would be just over 2.36 million.

Now let's fix the values showing in my bar chart. Going back to my solution, it's pretty clear that I need to take my current value which is the sum of revenue for a particular month in a particular region and divide by the revenue for that month excluding the region. This should be pretty easy to do given the LOD expression I've created already.

So, back in my exercise sheet I'll create another calculated field.

This time I'll call it RevPercentExRegion.

And in here I'll type sum of revenue divided by the sum of RevExRegion.

And I just need to close one bracket which the editor tells me.

Now I'll press OK.

Remove the value from the Columns shelf and add my new value.

This seems to be working correctly where in each month the four values corresponding to each region all add up to one. However, I will need to do some number formatting to make this chart more readable. To change the number format, I'll simply go to the dropdown, select Format, choose Pane and adjust the default, Percentage and remove the decimal places.

And this now gives me the chart that I want where I'm using the exclude LOD expressions to adjust my color coding and to adjust the labels on each of my bars. This has been quite a complex lesson. And so, to make sure that you understand the concepts involved, I'd encourage you to go back over this lesson again quickly making sure that you can create this chart yourself without any help. At the beginning, it's very useful to test your understanding of LOD expressions by performing the calculations in Excel in parallel when you're creating these charts. And this will ensure that the values you calculate in your visualizations are current. In the next lesson, I'll spend some additional time on the exclude function to make sure that you fully understand this important concept.

>
Formulas in Tableau
Level of Detail Expressions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial