CODEWITHSUNDEEP
tableau-apitableau-desktop
NidenK
NidenK

Reputation: 369

how to get previous year values in datetrunc?

I have a calc to get current year external hires, but I also want to make a field that gets the hires from last year.

if datetrunc('year',today())-1 = datetrunc('year',[Start Date]) then [Start Date] else null END

today()-1 would = 2020, but it isn't giving me any values, just null

Upvotes: 0

Views: 644

Answers (1)

Matthew Walkup
Matthew Walkup

Reputation: 76

The problem with DATETRUNC is that it leaves it as a DATE field. You cannot subtract 1 from a date field. You have to use a DATEADD function (see 2nd example)

It's easier to just use YEAR() (or DATEPART("year", [field])), but I prefer YEAR() because its shorter). This will change the data type to an integer, which you can subtract from.

IF (YEAR(TODAY())-1 = YEAR([Start Date])) THEN [Start Date] END

The else null is also unnecessary but can be included for completeness. It will be NULL if it doesn't match the IF statement.

The other option is to use the DATEADD function because you can use it to add intervals to a date field, but a little more tedious:

IF(
 DATEADD("year", -1, DATETRUNC("year", TODAY)) = DATETRUNC("year", [Start Date])
) THEN [Start Date] END

You still have to DateTrunc start date. This will basically set the month and day to 1.

Upvotes: 1

Related Questions