• 0

# case statement with datediff

## Question

Trying to do a case statement with results depending on # of days but get invalid formula Incorrect syntax near (.  Is it something simple I typed wrong or is this something that I cannot calculate?  This is for a calculated field in tabular report.

CASE
WHEN Datediff(day,[@field:DOH],GetUTCDate()) <= 30 THEN '0-30'
WHEN Datediff(day,[@field:DOH],GetUTCDate()) > 30 AND <= 60 THEN '31-60'
WHEN Datediff(day,[@field:DOH],GetUTCDate()) > 60 AND <= 90 THEN '61-90'
ELSE '90+'
END

## Recommended Posts

• 0

Seems to be an issue with the AND statement, when I remove that I do not get an error.

And was successfully able to get results using it like this:

CASE
WHEN Datediff(day,[@field:DOH],GetUTCDate()) <= 30 THEN '0-30'
WHEN Datediff(day,[@field:DOH],GetUTCDate()) < 61 THEN '31-60'
WHEN Datediff(day,[@field:DOH],GetUTCDate()) < 91 THEN '61-90'
ELSE '90+'
END

Seems it runs in order, so I do not need the extra operator

Looking at it again it may be because I did not include Datediff  after the AND.

##### Share on other sites

• 0

Hi @Fleshzombie,

I think the syntax with your AND condition is incorrect. I tried this one on my end and it gives me a Valid Formula.

CASE
WHEN Datediff(day,[@field:Date],GetUTCDate()) <= 30 THEN '0-30'
WHEN Datediff(day,[@field:Date],GetUTCDate()) > 30 AND Datediff(day,[@field:Date],GetUTCDate()) <= 60 THEN '31-60'
WHEN Datediff(day,[@field:Date],GetUTCDate()) > 60 AND Datediff(day,[@field:Date],GetUTCDate())<= 90 THEN '61-90'
ELSE '90+'
END

Hope this helps.

- kristina

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.