Tenure calculation using differences between 2 dates (magic etl)

Reply
Highlighted
Yellow Belt

Tenure calculation using differences between 2 dates (magic etl)

Hi,

 

I am creating a new variable using the add formula icon where I am creating time period bins that reflects the tenure of the consultant in reference to today. This is the formula I created:

case
when CURRENT_DATE() -`CD (Start)` < 90 then '3 Months'
when CURRENT_DATE() -`CD (Start)` >= 90 and CURRENT_DATE() -`CD (Start)` < 180 then '3-6 Months'
when CURRENT_DATE() -`CD (Start)` >= 180 and CURRENT_DATE() -`CD (Start)` < 270 then '6-9 Months'
when CURRENT_DATE() -`CD (Start)` >= 270 and CURRENT_DATE() -`CD (Start)` < 360 then '9-12 Months'
when CURRENT_DATE() -`CD (Start)` >= 360 and CURRENT_DATE() -`CD (Start)` < 720 then '1-2 Years'
when CURRENT_DATE() -`CD (Start)` >= 720 then '2+ years'
else 'Missing' end

 

But I get the following error message: Column of type date cannot be converted to type floating decimal. Surprising the formula above works in a beast mode. How come it is not working in Magic ETL?


Accepted Solutions
Highlighted
Black Belt

You should use the DATEDIFF function to get this. You can also simplify your statement due to the way CASE statements process. Here is what I would suggest:

case
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 90 then '3 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 180 then '3-6 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 270 then '6-9 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 360 then '9-12 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 720 then '1-2 Years'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) >= 720 then '2+ years'
else 'Missing' end



**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Black Belt

You should use the DATEDIFF function to get this. You can also simplify your statement due to the way CASE statements process. Here is what I would suggest:

case
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 90 then '3 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 180 then '3-6 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 270 then '6-9 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 360 then '9-12 Months'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 720 then '1-2 Years'
when DATEDIFF(CURRENT_DATE(),`CD (Start)`) >= 720 then '2+ years'
else 'Missing' end



**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.