Counting the number of breakdown elements that match a certain criteria

[graycarper]

Hello there!

Let's say we have a formula indicator called "% Incident Awesomeness" and it has a breakdown on Business Service. Let's say we then wanted to create a new formula indicator that counts the number of Business Services whose % Incident Awesomeness is greater than 60%.

For example: Business Service Unicorn has 99% Incident Awesomeness, Business Service Rainbow has 80% Incident Awesomeness, and Business Service Hurricane has  2% Incident Awesomeness. In this scenario, the new formula indicator would record a count of 2, since two of those are greater than 60%.

How would you go about defining that formula indicator?

In my imaginary world, I thought perhaps a script like this might work...

var count = 0;
for (var element in [[% Incident Awesomeness > Business Service]]) {
  if (element > 60) {
    count = count + 1;
  }
}
count;

...but apparently you can't iterate through Breakdowns using the '>' notation.

Any other ideas out there?

Thank you!

-Gray

[graycarper]

Hi, Josh!

Great news! I finally returned to this and implemented your suggestion. It worked! In case anyone else wants to do something similar, I'm going to describe exactly the steps I used...

Step 1: Click Indicator Sources within Sources under Performance Analytics in the left-navigation bar. Click New beside Indicator Sources. Set Name to Return.One.Record.Daily, Frequency to Daily, Facts Table to User [sys_user], and add a filter condition: User ID is admin. Click Submit.

Step 2: Click Scripts inside Automation under Performance Analytics. Click New beside Scripts. Set Name to Count.Business.Services.Higher.than.60%.FRR.Daily, Facts table to User [sys_user], Script to:

var count_elements = function(threshold) {
    var count = 0;
    var denominator = new GlideRecord('pa_scores_l1');
    denominator.addQuery('breakdown',17); // 'Business Service' breakdown
    denominator.addQuery('indicator','11'); // '# Resolved Incidents' Indicator
    denominator.addQuery('start_at','>=',score_start.getGlideObject().getDate()); // The beginning of the date range
    denominator.addQuery('start_at','<=',score_end.getGlideObject().getDate()); // The end of the date range
    denominator.query();
    while (denominator.next()) {
        var numerator = new GlideRecord('pa_scores_l1');
        numerator.addQuery('breakdown',17); // 'Business Service' breakdown
        numerator.addQuery('indicator','313'); // '# Two-Reply Resolved Incidents' Indicator
        numerator.addQuery('start_at','>=',score_start.getGlideObject().getDate()); // The beginning of the date range
        numerator.addQuery('start_at','<=',score_end.getGlideObject().getDate()); // The end of the date range
        numerator.query();
        while (numerator.next()) {
            if(denominator['element'] == numerator['element']) { // we have the same elements, so we can now do the calculation
               var score = (numerator['value']/denominator['value'])*100; // Calculate % Few-Reply Resolution
               if (score > threshold) {
                   count = count + 1;
               }
               break;
            }
        }
    }
    return count;
};
  
// This script will count the number of Business Services that have a % FRR greater than the number passed to the function below.
count_elements(60);

Step 3: Click Automated Indicators inside Indicators under Performance Analytics. Click New beside Indicators [Automated View]. Set Name to # Business Services with > 60% Few Reply Resolution (Daily), Frequency to Daily, Direction to Maximize, Unit to #, and Precision to 0. On the Source tab, set Indicator Source to Return.One.Record.Daily, Aggregate to Sum, Scripted to Enabled, Script to Count.Business.Services.Higher.than.60%.FRR.Daily, and Value when Nil to 0. Click Submit.

Thanks so much for your help!

-Gray

[Josh Cooper]

No problem!  Congrats on getting it going!  It's also awesome that you came back to post the method.  That makes it much better for everyone else who may run into something similar going forward.

[gyedwab]

If you in the future want to do this codelessly, and still be able to drill down, you can look at doing this with Explore Analytics.

What I did for this was... first, I created the report with the Incident Awesomeness % by Business service - in this case, creating a real-time report on two different tables (calls, in my MySQL database, and Incident, in ServiceNow), and applying a formula:

Once I had the view, I used the menu option to Create a composite view. This allows me to treat this "incident awesomeness" field as if it was a field in ServiceNow... then I can just filter on Incident awesomeness > 100%

 

Furthermore, I can also use 'binning' to compare the distribution of business services by % awesomeness: