Direkt zum Hauptbereich

Level of Detail Expressions (LoD) in Tableau: INCLUDE



I think LoD is the most popular calculation method if you are using tableau. Thus, you can find a lot of good blogs, whitepappers etc., to this topic.

There are three keywords in LoD: INCLUDE, EXCLUDE and FIXED.

However, why LoD is so popular? Why everybody discussed about this keywords? Where is the magic?

As you know, Tableau shows you data if you put this data to the row or to the column. However, what if you would like to count into some numbers, but you don't want to vizualize them?... The answer is LoD :)
Let's have a look to the first magic function: INCLUDE

With this function you can find an answer to such questions like:
- Which region has the highest average of order size?
- Which country in the sales database has sales reps who close the “biggest deals,” on average?


Here is the structure of this function:



If we modify the formula to :

AVG({INCLUDE [Order ID]:SUM([Sales])})

we would find an answer to the question: Which region has the highest average of order size?

With other words: with this formula we said to Tableau:

"Hey, Tableau, please INCLUDE the sale (SUM([Sales])) as an average to each orders ([Order ID]) always in account, even then when I don't show orders!"

Let's have a look to the practice:

Using the data from superstore, I created two calculation's fields:
1) AVG_LOD:    avg({INCLUDE [Order ID]:SUM([Sales])})
2) AVG Sales:     AVG([Sales])

and I created follow bar chart:


What did we calculate?

By the calculation AVG_LOD, Tableau calculated as a first step the average of all orders, and as a second step it calculated the average of this average for every region. So this is the answer to our question: Which region has the highest average of order size?

Note!
By this visualization we don't show the order ID but Tableau included this number by this calculation in the background.

By the calculation "AVG Sales" we just calculate the average of the sales per region. Here, Tableau didn't calculate an average per order, it calculated the average of sales.

You can find also the wihtpaper here.

This links could be also helpful:

- LoD: EXCLUDE
- LoD: FIXED
- Top 15 LOD Expressions (english)
- Top 15 LoD Expresions (german)
- Video: Introduction to LoD (english)

I hope this blog was helpful either... ;-)


Labels:

Kommentare

Kommentar veröffentlichen

Beliebte Posts aus diesem Blog

Tableau Table Calculation Function: WINDOW - Functions

The functions which begin with „WINDOW_...“ are also common used in Tableau. Remember! The “WINDOW_” function stays for the offset in data set, so-called WINDOW. It can look like this: 1) You can see the “WINDOW” clearly because of separation line between rows: 2) You limit the “Window” by giving the information about the first and the last row number. In this case, you give Tableau the information about the data offset. Let's have a look at the example with WINDOW_SUM I created a sample with data from Superstore. I would like to have a total sum of Sales in every row. In order to do this I created a calculation field: WINDOW_SUM(SUM([Sales]), FIRST(), LAST()) With this formula I said to Tableau: “Hey Tableau, calculate the total sum of sales from the first till the last row in the data set” And this is the result: Tableau wrote the result (total sum of Sales) in every row. As another option you can cumulate the result in each row and...
Kommentar veröffentlichen
Mehr anzeigen

I want to understand RegEx! (Part2)

It is already two weeks ago since my last blog post. This two weeks I spent in Scotland. It is a very beautiful country with unforgettable landscaps. However, it is not the topic I want to talk about. In this blog I will continue the topic about the RegEx and its using in Tableau. Tableau offers you four different uses cases of RegEx: REGEXP_EXTRACT If you have data like this: aspirin_20_mg_oral_tablet and you would like to extract onliy 20_mg. What I do is I check my RegEx on this web site: http://regexr.com/ If it is ok, put this RegEx in Tableau function: REGEXP_EXTRACT([0-9][0-9]+[_]+[a-z]+) and extract all "20_mg" REGEXP_EXTRACT_NTH The same like the function before but here you can limit your extract to Nth index. Image you have data like this: aspirin_20565435 and you want to extract only "aspirin_20". So your formula should be: REGEXP_EXTRACT_NTH([a-z]+[0-9], 2) REGEXP_MATCH It is very easy one: Using REGEXP_MATCH we can find if ...
Kommentar veröffentlichen
Mehr anzeigen

How create a calender with tableau?

I posted a calendar to tableau public, which I have done with Tableau 9.3. In my first blog I would like to share with you the steps for creating a calendar. The data sources and calendar you can find here. There are different reasons to use a calendar in tableau e.g. You can create your own calendar with an individual format and a lot of appointments. So it will help you make planning better.  You can also analyze things with calendar, if you drop&down a relevant dimension in order to show successful or not successful days.  To create such things is just a training and will help you use dimensions&figures on another way. And thi is my approach: I created a spreadsheet in excel with three columns: Date  Type of holiday  Holiday The column "Type of holiday" is necessary in order to make differences between private and official holidays. With the spell "C" I marked all private birthdays and with the spell "F" all offici...
8 Kommentare
Mehr anzeigen