Direkt zum Hauptbereich

Level of Detail Expressions (LoD) in Tableau: EXCLUDE

One of other keyword of LoD is the function EXCLUDE. If you have already read my article about INCLUDE than you will easier understand the function EXCLUDE.

Remember!
By the function INCLUDE we included some numbers into the calculation even though we didn't visualize this number.
By the function EXCLUDE we exclude some numbers from the calculation even through we visualize them.

However, let's us go step by step...

With the function EXCLUDE you can find answers to such questions like:
- What is the total slaes, as well as the total sales by region. Its means:
[(1) We need to exclude Region from our calculation of the monthly Total Sales
(2) And then include Region when calculating the regional Sales breakdowns.]

Here is the structure of this function:


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

"Hey, Tableau, please ignore (EXCLUDE) "Region" if you calculate sales (SUM[Sales])"

Let's have a look to the practice:

Using the data from superstore, I created a calculation's fields:
- Eexclude_sale:  { EXCLUDE [Region]:SUM([Sales])}

In order to have a comparison to the SUM(Sales) I created follow graph: 


On this chart you don't see any differences. You can ask: What sense does it make to create an LoD calculation field with "EXCLUDE" if I don't see any differences?  Please remember the number of Sales in the month November.

Than put region to the column and you will see differences:


Let's have a look to the month November.
You see in both regions in the first column (the left bar chart) the sum of sales (SUM[Sales]) and you see the differences between two regions. If you look to the blue bar chart, then you see, there are no differences between the regions. Compare this number with the number on the first chart. There are no differences. Tableau just ignored regions. This is the main idea of the function EXCLUDE. And this is the answer to our question.

Note!
By this visualization we do show the Regions but Tableau excluded this dimension by the calculation in the background.

 You can find also the wihtpaper here.

This links could be also helpful:

- LoD: INCLUDE
- 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

Tableau's Attribute Function ATTR()

There are a lot of blog posts about this function. However, for me the best one was written by Tim Costello. You can find the post here. ATTR comes from Attribute. An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such. If you take a look for Tableau Online Help , then you can find this definition: Tableau computes Attribute using the following formula: IF MIN([dimension]) = MAX([dimension]) THEN MIN([dimension]) ELSE “*” END I would say this is the main logic of ATTR function. With this formula you can understand how the ATTR function works. In simple words ATTR returns a value if it is unique, else it returns * Check out this links for mor examples: Drawing with numbers Powerful &Misunderstood: Tableau's ATTR Function Using ATTR Function It would be gerate if you share your examples of using of ATTR's function. Thank you!
1 Kommentar
Mehr anzeigen