How to get Latest Date in the DB in Tableau
How to get Latest Date in Tableau? When to use Today() and what are the problems with it and what is the alternate? Read this article for a solution.
When we are talking about Dates, generally when we want the latest Date, we tend to use Today() but this is not always the right choice coz it is not mandatory that the current day is the Latest Date in the Database.
Consider we have a Store and there will be certain days when it is closed. Now on the Day that the store is closed, what is our Latest Date in your Database. Yesterday right. Will Today() help here?
Another scenario. We have a Database which gets populated every Sunday. In the mid of the week, what will be our Latest Date? Will Today() help you get out of the scenario?
In one of my Project, we were having a Forecasted Data for that year and I was asked to get the Latest Date. Now, in the Data, I have Date exceeding Today(). Even here Today() is not going to help
So, when we are talking about Latest Date, just consider it as getting the largest out of a group of Numbers (Dates are special type of numbers called as Julian Numbers). We just compare the numbers with each other and get the largest number and when it is Tableau, we have MAX function to get the Greatest or Largest.
So, using Max(Date Field) will give the Latest Date. In the image below, I have used Super Store Data and applied Max on the Order Date field.
Now, see the image below and try to understand what is happening to Max(Order Date) field and why is it now showing the same day as Order Date and why is Today() showing Current Day for all the Rows.
Now, Order Date and Max(Order Date) are giving the same values all throughout. But as per what we have established earlier, Max(Order Date) should show 30–12–2019. Also when it is Today() why do we have same date for all the Rows? Pause the reading for while and implement this in your Tableau Desktop (or Tableau Public) and try to guess why is this happening.
Now, I know you have got what is happening in the background but let me elaborate so that we both are on the same platform. Tableau is a “ROW SLAVE”. That is because any manipulation that you do in Tableau will always and always happen at the Row Level only. Even when you aggregate all the Row Level values are aggregated to show the value it shows.
The same is happening in the above image. When we say Max(Order Date), if there are no other fields in the View, its is giving 30–12–2019 comparing all the Rows that are present in the DB. The granularity is at the DB level.
The moment I bring Order Date field besides it, now the granularity is at the Order Date. We have one Date per every Row in the View. So Max is calculated per Row and every Date is Max to itself (Also Min to itself). Hence you are getting the different Max Date for every Row which is same as the Order Date value in that Row.
Now we come to Today() Column. Here if we observe, there is same value for every Row. This is because whatever may be the Date in the Order Date field but Today is current Date right. IF we want to use something in place of Today(), it should get the same characteristics of Today() like it should be the same value for all the Rows like in Today().
Note: This is somewhat complex to understand. If this explanation doesn’t satisfy you, please mention your doubt in the comments so that I can give more explanation to it.
Now, try to answer a couple of Questions
Is the behaviour of Max(Order Date) is same as Today()?
Can we use Max(Order Date) in calcualtions as Today()?
Is the Granularity of Max(Order Date) and Today() same?
No Right. This is the reason, we need something more than these. This is where we use the LIFELINE of Tableau which is LODs. For me LODs are the GOD of Tableau. They are Omnipresent and Omnipotent. They can pull you out of the most complicated scenarios. For more information on LODs, visit my Youtube channel Rayapati
In LODs, there is something called TABLE SCOPED LODS. That is nothing but a fixed LOD without a Dimension part in it.
{ Fixed : Aggregation(Measure) } or { Aggregation(Measure)}
They just take what ever Measure with aggregation that you put in them and their Granularity is maintained at the DB level .
Note: Here DB level is used in a broader sense. Fixed LODs are effected by Data Source FIlters, Extract Filters and Context Filters.
If I say { Sum(Sales) }, they add up all the Sales and the value is shown for every row in the View.
The same way when I say { MAX(Order Date) } then for every row, it will show the same Order date which here is 30–12–2019.
If you are still confused, think of ROBO movie by Rajinikanth. When I want to compare all the people with Rajinikanth, I will create so many ROBOs equal to the number of people and keep one besides every person and compare.
Using LODs, we are able to get the Latest Date behave same as that of Today(). Now you can use it in all places where you used Today() earlier.
P.S — I tried to simplify the concept as much as I can but if you still didn't understand any point in it, please do mention in the comments so that I can get back to you with a proper explanation.
