Shulou(Shulou.com)06/03 Report--

Fake goods are rampant these days, and the level of detail calculation (that is, LOD Expressions) is no exception.

We know that the verbose level calculation of Fixed is not controlled by the dimension filter, while the table calculation is controlled by it.

When we use a LOD calculation of a table range to build a percentage, even if the filter selects only one value of the dimension (Accessories), it can maintain the original percentage, while the percentage calculated by the table automatically becomes 100 percent.

% by Table scopped LOD Calcs:

SUM ([Sales]) / SUM ({SUM ([Sales])})

However, sometimes you will find that Fixed's LOD calculations are also controlled by dimension filters, and such Fixed LOD calculations can be called pseudo-LOD calculations. For example, in the following figure, we use a Fixed calculation to calculate the average Sales of the three Category over time:

B

When only two values of Category are selected in the dimension filter, the average calculated with Fixed LOD will change! Why is this? What if the Fixed LOD calculation is not controlled by the dimension filter?

Avg by Fixed:

AVG ({FIXED [Category], YEAR ([Order Date]): SUM ([Sales])})

Taking a closer look, we find that the Fixed LOD calculation here is just to figure out the average Sales of each Category over time, and we can still calculate the average without Fixed LOD calculation:

Avg by Normal Calcs:

SUM ([Sales]) / COUNTD ([Category])

Therefore, it is not difficult to understand that dimension filters work on averages. So if we need to keep the average of Category unchanged no matter how we choose it, how do we create a calculated field? Obviously, we need a "real" Year (Order Date) to be calculated by Fixed's LOD:

Avg by Nested LOD:

{FIXED YEAR ([Order Date]): AVG ({FIXED [Category], YEAR ([Order Date]): SUM ([Sales])})}

At this point, when you select two Category or three Category, the average value remains the same.

According to the golden rule of embedded LOD computing (Golden Rule), when the inner layer is Include computing, the dimension of the outer Fixed can be passed on to the inner layer, so the above calculation field can also be changed to the following calculation formula, which is more concise.

{FIXED YEAR ([Order Date]): AVG ({INCLUDE [Category]: SUM ([Sales])})}

Let's look at another example. Let's build a map of states, and then build a LOD calculation for Fixed:

Category Sales:

{FIXED [Category]: SUM ([Sales])}

Since most states sell all three Category products, the Category Sales is basically equal to the total sales value of 2pm 297 Category Sales 201, which is marked with color.

However, when we choose to remove the Accessories of the Subcategory dimension from Control Filter, the value of Category Sales in South Dakota will change:

Why? Because South Dakota is very special, it has only Accessories Subcategory under its Technology Category. When we do not choose Accessories, we will lose the whole Category of Technology, so of course its Category Sales value will change.

So why do dimension filters affect the value of Category Sales calculated by Fixed LOD? Because here Category Sales is also a pseudo-LOD calculation! Instead of Fixed, we can get the same value of Category Sales with Include and Exclude LOD:

Alternative Category Sales:

IF COUNTD ([Category]) = 1 OR COUNTD ([Category]) = 2

THEN SUM ({INCLUDE [Category]: SUM ({EXCLUDE [State]: SUM ([Sales])})})

ELSE SUM ({EXCLUDE [State]: SUM ([Sales])})

END

Similarly, if we want the choice of Subcategory not to affect the value of Category Sales, we must Fix State this dimension, so that this embedded Fixed LOD calculation is really not controlled by the dimension filter.

Category Sales Fixed State:

{FIXED [State]: SUM ({FIXED [Category]: SUM ([Sales])})}

The LOD level of detail calculation is the most exciting evolution of all versions of Tableau. It is only with LOD,Tableau that it really stands out from many competitors. LOD makes complex calculations that were previously impossible or can only be solved at the data source level very simple. Master the LOD calculation, the Tableau level will have a substantial leap!

LOD computing is the key and difficult point of Tableau, which can only be understood and mastered by discrete thinking. Most Tableau beginners encounter the following problems:

(1) many business problems do not know how to create calculated fields to solve the problem.

(2) I don't know when to use LOD to calculate and what kind of LOD can be used to achieve the goal.

(3) I can't understand when I see the LOD calculation done by others.

(4) feel that their Tableau level has encountered a bottleneck and can not break through.

The six-hour video course "Tableau Master Advanced-in-depth understanding of LOD detailed level calculation" avoids boring definitions, concepts and formulas, strives to thoroughly explain the nature of LOD computing in an easy-to-understand language, and consolidates it with a large number of practical cases, so that you can establish a systematic LOD thinking and unconsciously use LOD computing to solve all kinds of business problems in practical work. Welcome to the 51CTO website to watch this course and give your valuable suggestions!

Course links:

Https://edu.51cto.com/course/20183.html?source=so

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.