0

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

NidenK
  • 321
  • 1
  • 8

1 Answers1

1

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.

Matthew Walkup
  • 76
  • 5
  • how would I do a count of rows in that table that fits the criteria? I have `IF (YEAR(TODAY())-1 = YEAR([Start Date])) THEN count([offers]) END` but I get a: cannot mix aggregate and non aggregate comparisons or results in 'if' expressions error – NidenK Aug 05 '21 at 19:25
  • You need to move the count around the IF statement: COUNT(IF (YEAR(TODAY())-1 = YEAR([Start Date])) THEN [offers] END) – Matthew Walkup Aug 09 '21 at 21:36