Creating a formula for turnover rate with two COUNT() and CASE functions

KevinjLayton

I'm trying to create a formula in my analysis that takes the count essentially pulling from 3 columns into two counts and doing math with the results (Termination Date within 90 days, Termination Category = Voluntary, and Hires within 90 days).

I'm trying to find the Turnover Rate from the voluntary terminations within 90 days so Count 1 / Count 2 *100 and then round it all to the first decimal. The problem I'm getting is that when I do this, I'm assuming the formula is too complicated for obiee to read because when I compile it there's no errors but it reads 0.0% across the board. Does anyone know how I can simplify this? or how I can get this done. It should be mentioned that I only have access to OBIEE and can't get to anything in the background. I'm running Oracle 11G.

Here is the current formula:

"ROUND(

(COUNT( CASE WHEN ("Worker"."Termination Date" >= TIMESTAMPADD(SQL_TSI_DAY, -(90), CURRENT_DATE)) AND ("Workforce Management - Worker Assignment Event Real Time"."HR Action"."Termination Category" = 'Voluntary') THEN 'x' END)) /

(COUNT( CASE WHEN "Worker"."Assignment Start Date" >= TIMESTAMPADD(SQL_TSI_DAY, -(90), CURRENT_DATE) THEN 'v' END))

*100,1)"

I tried to space it out so you guys can read it easier. Any input would be much appreciated.

Thanks

Gianni Ceresa

Hi,

I didn't really look at your formula as I blocked on a first thing: you have COUNT(...) / COUNT(...) * 100

=> you are doing integer / integer = an integer division, it can only return integers. Being close numbers it's probably the thing returning you 0 all the time.

You need to cast at least one of the 2 (if not the 2) to a float/double and multiply by 100.0 to keep the "not integer" format.

Try this, could already fix your issue...

KevinjLayton

That worked, thanks your a life saver!