TABLEAU: dump a field to get the last value
I am using Tableau Desktop, my data is like this:
KPI,date,monthValue
coffee break,01/06/2015,10.50
coffee break,01/07/2015,8.30
and I want to create a table like this
KPI, year(date), last value
coffee time, 2015, 8.30
How can I set the calculated field to show me the last value available this year? I have tried:
LOOKUP([MonthValue], LAST())
But it didn’t work and tells me: "You cannot mix unit and non-unit", so I did:
LOOKUP(sum([MonthValue]), LAST())
But that didn't work either. How can I proceed?
source to share
If you are using Tableau 9, you can do this with the LOD calculator, which looks for the maximum value in your date field and then checks if the current date value is the same as the maximum date value.
[Date] == {fixed: max([Date])}
As you can see in the example below, when you use calc as a filter, you only get the last line from your example above.
UPDATE: to get the values per year, you can do something like:
Here I am using a table calculation to find the maximum date in a year, and then rating those dates and filtering to the last date in each year (which will have a rank of 1).
!max date
WINDOW_MAX(ATTR(Date))
!rank
- RANK(Date)
You need to make sure that the table calculations are computer correct (in this case, per value).
source to share