Performance Analytics formula indicator not handling NULL very well

[Daniel Falarde1]

Performance Analytics formula indicator not handling NULL very well

Lets assume we have 2 automated indicators :

numProbSLAdue => collects the count of problems for which the SLA is due to expire today,

numProbSLAmet => collects the count of problems for which the SLA was met.

Now we have a formula indicator that gives us the percentage of problem SLA met :

percentProbSLAmet => ( [[numProbSLAmet]] / [[numProbSLAdue]] ) * 100

Now let’s assume there are no Problem SLA due today, then the count of numProbSLAdue would be 0 (zero) and a division by 0 is never a wonderful thing so lets add the following logic to our formula => percentProbSLAmet :

                // percentProbSLAmet formula indicator

var denominator = ([[numProbSLAdue]]);

                var numerator = ([[numProbSLAmet]]);

                if (denominator = 0) {

null

} else {

               ( numerator / denominator ) * 100

};

Now, using our formula percentProbSLAmet result, let’s make this a range based score, where we assign points based on achievement of a target, in a formula indicator => scoreProbSLAmet :

                // scoreProbSLAmet formula indicator

                var Score = (100);

                var Indicator = ([[percentProbSLAmet]]);

var Target = (80);   // OR a target set on the indicator and we use a method to get it.

if ((Indicator) == null) {null}

  else if((Indicator) >= (Target*(100/100))){(Score *(4/4))}

  else if((Indicator) >= (Target*(99/100))){(Score *(3/4))}

  else if((Indicator) >= (Target*(95/100))){(Score *(2/4))}

  else if((Indicator) >= (Target*(90/100))){(Score *(1/4))}

  else {0};

This, along with the Allow Formula Component to be NULL = true (checked) should produce a result of null from our example above where the denominator is zero, because it has null as an input and the first thing we do is test this.

Else, if the percentage is of 80 or above, it would get 100 points, if below 80 but at least 99% of the target, it would get ¾ of the points; 75 points etc…

Now this does not seem to work, whether I check the Allow Formula Component to be NULL checkbox or not.

When there is nothing to evaluate, we do not want to have a percentage of 0 (zero) or 100 because, there are no records in our formula denominator. Same goes for the scoring, if it is null so then it should be null. Why not ?  We do have a checkbox to handle that in our formulas.

Now let’s make this more complex by having separate indicators per Priority. So, the following Automated indicators are :

numP1ProbSLAdue => count of P1 problems for which the SLA is due to expire today,

numP1ProbSLAmet => count of P1 problems for which the SLA was met,

numP2ProbSLAdue => count of P2 problems for which the SLA is due to expire today,

numP2ProbSLAmet => count of P2 problems for which the SLA was met,

numP3ProbSLAdue => count of P3 problems for which the SLA is due to expire today,

numP3ProbSLAmet => count of P3 problems for which the SLA was met,

numP4ProbSLAdue => count of P4 problems for which the SLA is due to expire today,

numP4ProbSLAmet => count of P4 problems for which the SLA was met.

Then same thing for our percentage formula indicators (one per Priority):

                // percentP1ProbSLAmet formula indicator

var denominator = ([[numP1ProbSLAdue]]);

                var numerator = ([[numP1ProbSLAmet]]);

                if (denominator = 0) {

null

} else {

               ( numerator / denominator ) * 100

};

Also a percentP2ProbSLAmet formula indicator etc… for P3 and P4…

And you guessed it, scoreP1ProbSLAmet formula indicator, one per priority so we get separate score for each Priority individually. Note that we could also have made a Priority breakdown and used it but, let<s keep it simple for now.

Now it is not impossible that we find 0 (zero) records for P1 Problems but some records for P2, P3 and P4. So let<s assume it is the case where P1 outputs ‘null’ and the rest all have percentages and a score.

Let’s say P2 would have 50 points, P3 - 100 points and P4 - 75 points. We would think that the summation of this would be ‘null’ + 50 + 100 + 75 = 225 points out of a possible 400 points. Again, considering that the Allow Formula Component to be NULL checkbox is checked.

Unfortunately nope !!!  Apparently, when ServiceNow has PA Formulas, it first tests all its components individually first before applying the final calculation. Now because the resulting value of the P1 calculation is ‘null’ or not a number, it stops right there, whether the NULL checkbox is checked or not so, does it allow the formula components to be NULL, the answer would unfortunately be nope !!!

I had a case opened for that and ServiceNow created a Problem record that eventually got closed. I am not convinced that the issue was well understood.

How can we work around this without creating an enormous formula… ?

Any ideas would be greatly appreciated.