Tableau: last value calculation in calculated field

Question

my dataset is like this

KPI                    VALUE    TYPE     DATE  
coffee break duration   11        0      30/06/2015  
coffee break duration   12        0      31/07/2015  
coffee break duration   10        0      30/11/2014  
coffee break duration   10        0      31/12/2014  
coffee expense          20        1      31/07/2015  
coffee expense          20        1      31/12/2014  
coffee consumers        15       -1      31/07/2015  
coffee consumers        17       -1      31/12/2014

for Type, 0 means minutes, 1 means dollars and -1 means people

I want to get a table like this

KPI                     Year(date)    YTD  
coffee break duration    2015         11,5  
coffee break duration    2014         10  
....

YTD calculation is:

if sum([TYPE]) = 0 then avg([VALUE])
elseif sum([TYPE]) > 0 then sum([VALUE])
elseif sum([TYPE]) < 0 then [last value for the considered year]
end

By [Last value for the considered year] I mean the last entry available, in a year if my table is set to Year, otherwise it has to change dynamically based on what Timespan I want to show.

What can I do to have [last value for the considered year] as a calc field ready to use in my YTD calc?

Many thanks,
Stefania

Create a new calculated field IF MAX(DATE) THEN VALUE. Place this Calculated Field in your if statement — FirebladeDan
@FirebladeDan "the calculation contains error: expected type boolean, found date. Comparison in 'IF' expression must be boolean type." — stegue88
WINDOW_MAX(IF DATE = MAX(DATE) THEN VALUE)... Right click this pill and group by date or something of that nature. I would have to look at your workbook :/ — FirebladeDan

Vadym B. Vadym B. · Accepted Answer · 2015-08-21T07:11:53

If I understand your question, than you can use LOD in the IF statement

if sum(type) = 0 then avg([value])
elseif sum([type]) > 0 then sum(value)
elseif sum([type]) < 0 then max(if [date] = { INCLUDE kpi: max(date)} then [value] end)
end

If there are several values on the last day of the considered year, it would take the biggest value

I slightly modified your data to show that results are working correctly

Tableau: last value calculation in calculated field

1 Answers