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

[Michael Fry1]

Sounds like Bucket Groups would work: https://docs.servicenow.com/bundle/kingston-performance-analytics-and-reporting/page/use/performance...

You can push results into different 'buckets' and get your count from there.

[graycarper]

Hi, Michael!

Ooh. Bucket Groups. Yes, that does seem like it would give me precisely what I'm looking for. I attempted to implement one, but I ran into a roadblock: In order to create the necessary Breakdown Mapping I need a Facts Table to get the data from. The data comes from a Performance Analytics Formula Indicator, and as far as I know those are calculated in real-time when a widget displays the data, not stored in a table I can query (or associate with a Breakdown Mapping). Is that right? If so, I'm afraid Bucket Groups won't fit the bill. 😞 If you know of a table I could query to get Formula Indicator results, though, then I think we'd have our ideal path to a solution.

Thanks for your help!

-Gray

[Josh Cooper · Accepted solution]

Neat question!

So, I'm not sure if there's a quicker way to do this, but here's a method that will work.

Build a new Indicator Source against any table, but filter the results so that you get exactly one result.  Something like sys_user where User is graycarper, or Incident where Incident Number is INC0001001, etc.  The table doesn't matter as long as the result is one

Then, build a new automated incidator, and change Count to Sum.  

Add a script, and have it process exactly as it should in the formula, pulling scores in either from the pa_scores table or the table that the records came from originally.  When you 'return' a number from the formula, it becomes the score for that indicator, regardless of where it came from.  

The drawback is that you won't be able to drill into records this way, but if it's just a supporting score, that should be okay.  

Incidently, breakdowns still work on scores created this way, you just have to get a little more creative.  Instead of returning precisely one row, set it up the same way you would your Breakdown source - then each 'current' record will have the breakdown value, and you can use that in a regular breakdown to determine which bucket it should go in.  Remember scripts for Indicators want a value.  Scripts for Breakdowns want a sys_id that matches the Breakdown Source.  As long as you're returning appropriate values, PA will handle the rest.

[graycarper]

Fascinating! Today I explored the Bucket Groups idea that Michael suggested, but I'll try your suggestion next. Thanks, Josh!