Best Of

I was suggesting re-writing total months on SaaS to this:

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
END

However, since you are wanting the total months and not the number of days, you will need to do it slightly different. You can use the Period_Diff function to calculate the month difference like this:

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
END

Hope this helps.

@Abhijith how did you go?

Hi @Abhijith, so you need two calculated fields for 'Exit' and 'Employed/Headcount'. Using the fields from the ETL, the Exit beast mode would like:

''' SUM(SUM('Terminated')) OVER (PARTITION BY YEAR('Last Day of Month'), MONTH('Last Day of Month') ORDER BY 'Last Day of Month')

You would then need to calculate the rolling average headcount for each month (year vs prior year), which I don't know how to code dynamically.

After getting those two working, you would combine to create the annual turnover rate calculation.

Nice! Have you noticed any changes in values to the Monthly Rolling Turnover?