At this post, I will continue with EXCLUDE LOD. The dataset info and testing conditions are explained at an other post.
Explanation of data : Link
Fixed LOD : Link
For this post we use LOD below.
EXCL STATE ORDERS
{EXCLUDE [State] : COUNTD( [Order ID] ) }
- EXCLUDE LOD
At above we can see, it is a count query,without any dimension. SQLLite in Pandas is as below.
2) State
Now lets put our variable State into picture. Since State was what we wanted to exclude it still gives 20, which is very nice.
The Tableau SQL is as below. The gist of query is “CROSS JOIN” :
A cross join returns the Cartesian product of rows from the tables in the join.
It combines each row from the first table with each row from the second table
So we can check the inner query with Cross Join.
By State
By Count
And Cross Join copies above 20 to all rows of previous query.
3) State City
When we have both State and City in the view, counts are taken ignoring State, using City. The count query is over city group by City.
4) Country State City
When we have Country,State and City in the view, counts are taken ignoring State, using Country, City. The count query is over city group by Country,City .
5) Country City
When State is not at view, query gets very simple.(Since we are already ignoring it, Tableau SQL becomes 1 simple SQL.)
6) Category
When we have only Category, again we see a very simple query just over Category.
7) Category State
Since we have State at view, at inner sub query “t0” we use Category and State together. At “t1” we only count over Category. We join these queries on field Category. This way we ignore State, in calculation of numbers.
In this post, I showed examples of how Tableau SQL queries behind EXCLUDE LOD. It is good to know what Tableau is really doing behind.
