I was recently working with a universe
and attempting to calculate a ratio between two numbers and coming up with some strange results. I wanted to share this with you so you didn’t waste as much time as I did trying to solve the problem.
Always Returning Zero
In my example, I had two measures and i wanted to calculate the ratio between this. I was working on a call center universe and I needed to calculate First Call Resolution Ratio. That would be First Calls/Total Calls. The problem was that my calculation was always returning 0. Here is what the SQL looked like:
select count(dbo.Current_Facts.first_call) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts
There is nothing wrong with the SQL. The problem was with the data type. In my case, both the numerator and denominator are integers, therefore the resulting value is also an integer. Since there are always less first calls than total calls, the values would be a fraction and therefore were truncated to 0.
An Easy Fix
Once you realize the problem, the fix is simple. Simply modify the numerator to be a float value. This will cause the calculate to result in a float value. In my case I changed the first_call value as casted it as a float so my SQL came out like this:
select cast(count(dbo.Current_Facts.first_call) as float) /count(dbo.Current_Facts.call_id)
from dbo.Current_Facts
Although this should fix the problem, there are probably databases that may require you to be more specific in which case you might modify the SQL to look like this:
select cast(cast(count(dbo.Current_Facts.first_call) as float)/cast(count(distinct dbo.Current_Facts.call_id) as float) as float)
from dbo.Current_Facts
Hopefully this little tip will safe you tons of time spent scratching your head.
«Good BI»