Skip to main content
berin_bezmen_9a2cd6
berin_bezmen_9a2cd6
Contributor ⭐️⭐️⭐️
April 6, 2017
Question

Calculated Field with value zero

  • April 6, 2017
  • 7 replies
  • 143 views
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

    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
    karl_rumelhart
    Expert ⭐️
    August 16, 2018
    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_9a2cd6Author
    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
      Expert ⭐️
      August 16, 2018
      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. 
        mark_deegan
        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 & ConditionsAccessibility statement