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 '36 Months'
when CURRENT_DATE() `CD (Start)` >= 180 and CURRENT_DATE() `CD (Start)` < 270 then '69 Months'
when CURRENT_DATE() `CD (Start)` >= 270 and CURRENT_DATE() `CD (Start)` < 360 then '912 Months'
when CURRENT_DATE() `CD (Start)` >= 360 and CURRENT_DATE() `CD (Start)` < 720 then '12 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

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 '36 Months' when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 270 then '69 Months' when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 360 then '912 Months' when DATEDIFF(CURRENT_DATE(),`CD (Start)`) < 720 then '12 Years' when DATEDIFF(CURRENT_DATE(),`CD (Start)`) >= 720 then '2+ years' else 'Missing' end
1