TABLEAU: calc field to get the last value available

Question

I'm using Tableau Desktop, my data are like this:

KPI,date,monthValue

coffee break,01/06/2015,10.50

coffee break,01/07/2015,8.30

and I want to build a table like this

KPI, year(date), last value

coffee time, 2015, 8.30 

How can I set a calculated field in order to show me the last value available in that year? I tried to do:

LOOKUP([MonthValue], LAST()) 

But it didn't work and tells me 'can not mix aggregate and non-aggregate', so I did: 

LOOKUP(sum([MonthValue]), LAST())

But it didn't work too. How should I proceed? 

Answer 1

• If you are using Tableau 9 then you can do this with a LOD calculation that looks for the max value in your date field and then checks if the current date value is the same as the max date value.

[Date] == {fixed: max([Date])}

• As you can see in the example below when you use the calc as a filter you will only get the last row from your example above.

• In order to get the values per year you can do something like:

• Here I used calculation to find the max date per year and then ranking those dates and filtering down to the latest date in each year (which will be the one that has a rank equal to 1).

!max date is WINDOW_MAX(ATTR(Date))

!rank is RANK(Date)

• You need to make sure that the table calculations are computed in correct way (in this case across the values of each year).

Firstly register yourself in an industry-recognized Tableau Certification such as you can register for our Tableau Training Course.