This is the second part of post about SQL behind FIXED LOD. This time, I will show a more complicated case.
Read the first post for better understanding : Link
FIX STATE ORDERS:
{FIXED [State] : COUNTD( [Order ID] ) }
We have our FIXED LOD as before. Now we have 2 unrelated field in view.(FIXED LOD has State.)
HOW IT FINDS 7 and 10 below !!!
SQL by Tableau is a below.
Pandas give same result.
If we execute only 1st query result is very simple to understand.
Now you still wonder how the calculation of 7 and 10 came. After the inner join of 2 generated tables, we are infact calculating below temp table.
So we are infact summing the number of orders of Category at that state.
Now only think Kentucky, USA.
It has 4 distinct orders, and some of them have Furniture. Then If we have a row in any calculation ,it will have 4 as numeric values of FIXED LOD.
Furniture,Kentucky -> 4
Same for Pennsylvania, it has 2 orders, and have at least 1 line with Furniture, so calculated value will be 2.
Let’s check Category only. Remove Country from rows, and put to filter.
We can see same query, is executed, this time no “Country” at select. Again the story is about how to join Fixed LOD calculation to the view when it is not there.
In this post, I think I investigated , not a very clear case about FIXED LOD. This backend debugging, reveals how these values are calculated in depth. When calculation does not make sense with a FIXED LOD, think :
it is being calculated in another dimension, and the value related to that row is, FIXED LOD value associated with that row.
