Syntax error trying to capture dates within the last year

Reply
Highlighted
White Belt

Syntax error trying to capture dates within the last year

Hi,

 

This code is returning "Invalid Formula : This calculation contained a syntax error. "

 

(CASE
    WHEN CURRENT_DATE() - `Date Open` <= 365  THEN `Date Open`
    ELSE 0
END)

 

Can you see the error?


Accepted Solutions
Yellow Belt

Re: Syntax error trying to capture dates within the last year

Hey @klagrangian,

 

The reason you are getting error is you are trying to get output in two different data type - One in date format and other in number. 

 

Instead of giving zero value for else statement - you can give one date value like  DATE('2399-12-31')

ex:

CASE WHEN DATEDIFF(CURDATE(), `hire_date`) <= 365 THEN  hire_date ELSE  DATE('2399-12-31')  END 

 

Other option is to convert date value to string:

 

CASE WHEN DATEDIFF(CURDATE(), `hire_date`) <= 365 THEN  CONCAT(' ',hire_date) ELSE  '0' END 

 

 

 

 


All Replies
Yellow Belt

Re: Syntax error trying to capture dates within the last year

Hey @klagrangian,

 

The reason you are getting error is you are trying to get output in two different data type - One in date format and other in number. 

 

Instead of giving zero value for else statement - you can give one date value like  DATE('2399-12-31')

ex:

CASE WHEN DATEDIFF(CURDATE(), `hire_date`) <= 365 THEN  hire_date ELSE  DATE('2399-12-31')  END 

 

Other option is to convert date value to string:

 

CASE WHEN DATEDIFF(CURDATE(), `hire_date`) <= 365 THEN  CONCAT(' ',hire_date) ELSE  '0' END 

 

 

 

 

White Belt

Re: Syntax error trying to capture dates within the last year

Thank you! Fixing the output data was the issue. I'm onto the next challenge now:

 

I'm showing this as a bar chart. I need to next add a second bar on the card for `Date Closed` that also shows dates withn the past 365 days without being influenced by the dates in the `Date Open` field. Here is what I changed the code to.

 

CASE WHEN DATEDIFF(CURDATE(), `Date Closed`) <= 365 THEN `Date Closed`
WHEN DATEDIFF(CURDATE(), `Date Open`) <= 365 THEN `Date Open`
ELSE DATE('1399-12-31')
END

 

This results in entries additional entries being to the `Date Open` bar, though 'Date Closed` bar has the correct total. Do you see my logic mistake here?

 

Yellow Belt

Re: Syntax error trying to capture dates within the last year

Hey @klagrangian,

 

Could you share the screenshot of what you are trying to visualize? 

 

I would suggest to create 2 different beast mode field - one for Date closed and other for Date open. 

 

Date Open: 

CASE WHEN DATEDIFF(CURDATE(), `Date Closed`) <= 365 THEN `Date Closed`
ELSE DATE('1399-12-31')
END

 

Date Closed:

CASE WHEN DATEDIFF(CURDATE(), `Date Open`) <= 365 THEN `Date Open`
ELSE DATE('1399-12-31')
END

 

Then use the Grouped bar to visualize. 

 

 

Yellow Belt

Re: Syntax error trying to capture dates within the last year

I used the wrong column in the case statement. Here is the correct one:

 

Date 'Closed: 

CASE WHEN DATEDIFF(CURDATE(), `Date Closed`) <= 365 THEN `Date Closed`
ELSE DATE('1399-12-31')
END

 

Date Open:

CASE WHEN DATEDIFF(CURDATE(), `Date Open`) <= 365 THEN `Date Open`
ELSE DATE('1399-12-31')
END

White Belt

Re: Syntax error trying to capture dates within the last year

Thanks for responding. I am trying to comparte the counts of records that have a "Date Closed" date within the last year with the counts of "Date Open" in the last year. A simple grouped bar chart of those two.

 

But when I create the two beast modes, one influences the other. Both bars are the same height, have the same total, which isn't the case. 

 

Yellow Belt

Re: Syntax error trying to capture dates within the last year

Hey @klagrangian,

I can take a look if you are able to share the screenshot of your sample data and domo card?

 

White Belt

Re: Syntax error trying to capture dates within the last year

Thank you for holding. Ultimately a co-worker helped solve the issue. The way the data is organized, with the date fiels being spread across multiple columns, was the issue. Using the ETL to pivot the date fields into a single column causes the calculated fields to count correctly. I'll mark this case as resolved. Thank you for your help in this.

Announcements
Domopalooza 2018! Early bird pricing is still available! Click here!