Tenure calculation using differences between 2 dates (magic etl)



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:

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?


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔵
    Accepted Answer

    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:

    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
Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!