Tuesday, August 3, 2021

Tableau LOD Calculations Explained with Ranking / Window functions

I started my learning journey with Tableau few months ago. 

So far , it is pretty good. However , I found LOD ( level of detail) expressions are interestingly complex 

at the first encounter.  Reading thru few examples and watching few videos to get a better understanding of these concepts. 

Being I come from database background and SQL knowledge , I am hoping , I can provide my understanding of LOD thru ranking/ analytical 

functions. 

Please see my blog posts on ranking / analytical functions  here 

https://mfzahirdba.blogspot.com/2009/10/analytics-20.html

https://mfzahirdba.blogspot.com/2009/11/analytics-20-part-ii.html

https://mfzahirdba.blogspot.com/2018/10/windowing-clause-why-it-matters.html

https://mfzahirdba.blogspot.com/2013/08/oracles-analytical-functions-vs-sql.html

https://mfzahirdba.blogspot.com/2012/09/difference-between-percentilecont-and.html


There are three LOD keywords ( FIXED , INCLUDE , EXCLUDE) in Tableau. 

FIXED LOD calculations does not depend on the visuals ( aka viz) , where as visulation impacts 

how INCLUDE and EXCLUDE are calculated. 

In my example , I have created a table in SQL server and populated few records. 

To follow along the example , feel free to use the script below. 


Create table CourseEnrollment 

( StudentName varchar(50) , 

  Department varchar(50) , 

  Subject varchar(50) , 

  EnrollmentDate Date 

 ) ;

 GO 


Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Maths' , 'Pre Calculus' , '1988-03-01') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Maths' , 'Multivariate Calculus' , '2010-05-16') ;

Insert into CourseEnrollment values ( 'Zahir Mohideen' ,  'Statistics' , 'Statistics 101' , '2020-07-25') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' ,  'Maths' , 'Pre Calculus' , '1991-03-01') ;

Insert into CourseEnrollment values ( 'Abdul Hameed' ,  'Physics' , 'Phy101' , '2020-07-25') ;


I have connected Tableau to my underlying table . 



Here , I want to know the very first enrollment of the student no matter what department / course he enrolled into . Also , this value does not change , when the viz is changed. 

In the "sheet" tab , I create a calculated field for the FIXED LOD  , by clicking on "Create Calculated Field " as below. 



Similarly , create LOD calculations for INCLUDE and EXCLUDE. 



Let us bring in these LOD calculations into the sheet . 


The value for First Enrollment Date - Fixed is the minimum enrollment date   per student , no matter what the dimensions are available in the viz.  FIXED by Student Name .

The value for First Enrollment Date - INCLUDE is the minimum enrollment date  per student and all other available dimension in the viz . In our case , Department . So , in essence , first enrollment date per student / department. 


The value for First Enrollment Date - EXCLUDE is the minimum enrollment date  per student and all other available dimension in the viz , except student name as specified in the LOD  . In our case , Department . So , in essence , first enrollment date per department. 

These results can be derived from SQL using analytical / window functions as 


When we remove the department  / Student , the FIXED LOD remains the same ; there is a change in Include and Exclude. 







Hope , this simple example provides the LOD in the eyes of SQL developer. 

I offer support for database maintenance , performance tuning , database development. Please contact for any database related activities. 






No comments:

Post a Comment