In this post, I will show different calculations, different views with FIXED LOD. There are lots of nice tutorials, I will not repeat everything. Just will try to show, how to think on it, by clear samples.
Here we have a simple dataset. I created myself, to better understand the data. Basically we have 2 identical data for Turkey and United States. I deleted the data and only have 14 rows per country, so total 28.
Data is at : Link
It is a super simplified version of , famous Tableau Sales Superstore data. Most of time, I filter data by Country. So be careful. When there is aggregation by Country or upper level, I remove filter.
When I clone for a 2nd country(Turkey) , I appended “x”, to strings.(City,State,Customer..) and some dummy numbers to ID(OrderId,CustomerID)
Explanation Of Set
10 Orders
7 Customers, with number of orders in 2nd columns
6 States with distribution of orders, 4 Kentucky,2 Pennsylvania
By State,City view
Each Country is identical with 10 Orders
Planet point of view
We create a State FIXED LOD :
{FIXED [State] : COUNTD( [Order ID] ) }
Here we create a metric at level of State, we keep data at level State.
Our hierarchy :
Planet -> Country -> Region -> State -> City
Tabular View
We can see the breakdown of State,CustomerID and number of distinct order as below.
Using Fixed LOD
Now lets use our Fixed LOD, under the column “#Order By StateDIM”, we see number 4 for both Henderson,and Louisville. This is because it is the value of State they belong, and is is calculated ignoring the detail in View.
At below we see breakdown of Country,State. When we sum “Sum of # Order By StateDIM”, we get 10 as expected. If we do “Distinct count of # Order By StateDIM”, we get 1 for all States, because there is only 1 value calculated per state.
At below, If we check this from Country point of view, 10 orders from 7 distinct customers.
Sum #of Order by StateDIM = we sum all orders their sum will also make 10
Distinct count #of Order by StateDIM = how many different values we have in this sub StateDIM table. Only (1,2,4). 4 Kentucky, 2 Pensylvania, and other 1. So value here will be 3.
Another way to check our data is, by only putting FIXED LOD, into rows. So we said before we have 3 distinct values. (1,2,4). We can see that distinct count of Customer Id and Order Id , gives expected number for this measure. We do this by 8 city, 6 state…
View in deeper detail level
We have city which is at a deeper level than state. So For example for Kentucky we see number as 4. Because that metric is at level of State, and cities of Kentucky (Henderson , Lousville) have value 4 for Kentucky . At a deeper level we see data at FIXED LOD level.(State here). So for city , we have the value of State.
We will see same results even if we remove State.
View in same detail level
When level is same, we see exact numbers for state as below. Kentucky = 4 and Pennsylvania 2.
View in upper detail level
At country level we do not know state. What we know is , how many orders we have at state level. So we aggregate ,we have 10 orders in a country,( 4 from Kentucky,2 Pennsylvania … ). So we aggregate and find 10.
I created a planet column also. What happens when we go to 1 more up level. So aggregate again. In planet Earth we have 2 countries with 10, so they will make 20, if we sum.
Summary
There are 3 cases , Upper, Deeper, Same Level.
- View has data at upper level
There are 2 countries at up level. So we will aggregate.
How many countries have 1 ordered customers = 2 countries
How many countries have 2 ordered customers = 2 countries
How many countries have 4 ordered customers = 2 countries
Explanation of columns in order:
1)FIXED LOD, has 3 distinct values = 1,2,4
2)All these distinct values, exist in 2 different country
3)They come from 7x2 distinct customer
4)They make 20 distinct orders. Which are distributed to 1,2,4.
5)For FIXED LOD, States with 1 Order :has 8 Order(2Country x 4 different order), States with 2 Order : has 4 orders( 2country x 2 Order), States with 4 orders has 8 orders (2 Country x 4 Order)
Lets exaggerate this and do it at planet level.
How many planets have 4 ordered customers = 1 planet
How many planets have 1ordered customers = 1 planet
Since we go even 1 more level up,we ended up even with more aggregation to up.
2. View data has lower level
How many cities has 4 ordered customers = 2 city, Why because State Kentucky has 4 orders, and 4 is the values for both of it’s cities.
When we go to one deeper level, city, we do not know which city they below. So both cities under Kentucky (Henderson,Lousville) , can have the title of “cities belonging to a state of 4 ordered customer”.
3. View data has same level
As expected when we deal with results with same level of granularity with data, we expect to see exact same calculations.
Above we see FIXED LOD has 3 values. (1,2,4)
Distinct count of Order ID : For all these levels, how many different orders we have.
Distinct count of State :For all these levels, how many different States we have.
#Order By Customer
{ FIXED [Customer ID] : COUNTD([Order ID])}
We can create another FIXED LOD from Customer point of view and get same results as below.
In summary you can think of LODs, as added new data at level specified.
