Skip to main content
Question

Calculated Field with value zero

  • April 6, 2017
  • 7 replies
  • 95 views
berin_bezmen_9a2cd6
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? 
berin_bezmen_9a2cd6

7 replies

sundar
  • sundar
  • Expert ⭐️
  • April 13, 2017
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.
juhno_mann
  • juhno_mann
  • Contributor ⭐️
  • August 16, 2018
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.
karl_rumelhart
Forum|alt.badge.img+4
Hi, I assume you are using Bionic Rules, right?  Can you share more detail of what you are trying to do.
juhno_mann
  • juhno_mann
  • Contributor ⭐️
  • August 16, 2018
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?
berin_bezmen_9a2cd6
  • berin_bezmen_9a2cd6
  • Author
  • Contributor ⭐️⭐️⭐️
  • August 16, 2018
That's what I've been doing. I just added a filter that excludes results with 999999 as I couldn't find another solution.
karl_rumelhart
karl_rumelhart
Forum|alt.badge.img+4
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. 
juhno_mann
mark_deegan
Forum|alt.badge.img+7
  • mark_deegan
  • Contributor ⭐️⭐️⭐️
  • October 13, 2021

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?

 

 

Terms & ConditionsCookie settingsAccessibility statement