Question

Calculated Field with value zero

  • 6 April 2017
  • 7 replies
  • 59 views

Userlevel 5
Whats the best way to create calculated fields in the rules engine with fields having "0" as value? Whenever I created calculated fields (for example average of email sends this month compared to average of email sends to the prior month) and it is showing a 99999 increase even though they have sent zero emails in the last 2 months. Is there a best way to avoid the miscalculation? 

7 replies

Userlevel 6
You can filter out the records which has zero emails in last two months in step 1.
Or in action filter you can filter out the records that has 999999 as the increase in percent usage.
How would you go about filtering this out in step 1? I'm having the same issue when doing a 30 day period over period comparison with our usage.
Userlevel 6
Hi, I assume you are using Bionic Rules, right?  Can you share more detail of what you are trying to do.
Yes, bionic rules. We are doing a 30 day period over period % comparison and it works great when a customer has enough usage to calculate this. The issue is when we have a new customer that has no usage the first 30 days and and the calculation is returning a 999999 because it's dividing by 0. I can remove some of this by using a contract effective date, but some customers take longer than others to implement so they may be within the criteria, but haven't incurred usage yet.

I would ideally like to exclude any records that don't have any usage in the first period of comparison, but don't know how to do this. Would it be easier and just as effective to filter out records with 999999?
Userlevel 5
That's what I've been doing. I just added a filter that excludes results with 999999 as I couldn't find another solution.
Userlevel 6
Thanks for the context, Juhno.  The idea that you and Berin hit upon makes sense to me.  It would be possible to exclude records with no usage but I think it would require additional transformation and merge tasks so this approach is definitely simpler.  Having a way to automatically exclude records in the 'divide by zero' situation is a reasonable feature request, as well. 
Userlevel 2

Has this logic changed lately?

 

I’m Running a rule that compares last months usage to the prior months usage and when i divide by 0 i’m getting an result of 0?

 

My customers health scores are working of the principle of month on month growth, but yet if i have no usage in the prior month and usage this month its evaluating to zero - surely this isnt correct?

 

 

Reply