0 votes
1 view
in BI by (17.6k points)
edited by

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? 

1 Answer

0 votes
by (47.2k points)
  • 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.

enter image description here

  • 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).

enter image description here

!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).

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...