Calculating Minimum Dates Without Using LODs

by Hesham Eissa

Usually, to calculate the minimum date you would use an LOD calculation to get it – the equation would be {Fixed [Dimension] : Min([Date])} and in case you want to get the minimum date for the whole dataset (to the lowest level of detail) you would use {Min ([Date])}.

Unfortunately, clients often use an old version of Tableau that might not have LOD feature in it.

So how to create the view below without using an LOD calculation?

  1. Place [Order Date] onto the columns shelf and profit onto the rows shelf and sub-category onto the detail shelf
  2. Calculate min order date using a table calculation: [Min Order Date] =  WINDOW_MIN(MIN([Order Date]))
  3. Calculate the difference between the first day and order date: [Difference From First Order Date] = DATEDIFF(‘day’, [Min Order Date], Attr([Order Date])). You would need to use Attr([Order Date]) – otherwise you would receive an error because First Day is an aggregate and Order Date isn’t and Tableau cannot mix both. You can also use Min or Max, doesn’t really matter in this case.
  4. Drag [Order Date] into the detail shelf – this step is imperative because you can only perform Table Calculations on measures that are in the view!
  5. Right click on [Difference From First Order Date] pill and select edit table calculations and make sure to check [Order Date] box and uncheck subcategory box – which is basically telling Tableau “ FOR EACH [sub-category], calculate profit BY [Order Date]”
  6. To reach the view in top, all you need is to add a Running Sum of Total Profit and also make sure to check the [Order Date] box and uncheck [sub-category] box

Fri 21 Jun 2019

Thu 20 Jun 2019

Wed 19 Jun 2019

Tue 18 Jun 2019