Doing More With Formula Indicators

[Adam Stout]

The discussion below is intended for advanced Performance Analytics users who have 6 or months experience with Performance Analytics and the same or more experience with the ServiceNow Platform.   The advanced topics covered assume that you understand the basics of Performance Analytics and have a working knowledge of JavaScript.

Introduction

Performance Analytics Formula Indicators are often used for simple calculations.   This is incredibly useful for quickly adding in a new KPI that is a percentage based on existing data or calculating a "net" value by taking the incoming count minus the closed count.

But did you know they can do a lot more than that?

For example, we can use formula indicators to:

• Rounding a score to the nearest quarter of a point
• Display a constant value on a scorecard
• Show or hide manual scores based on the date
• Transform the score on the fly based on other information in the instance

Formula Indicators are JavaScript blocks that are processed on the server when called via the API.   This means that anything you can do in JavaScript, you can do in a Formula Indicator.

Remember, just because you can, doesn't mean you should!!!

Before we get into some examples, let's review some very important items to consider when using Formula Indicators. 

Performance

The Formula indicators give you lots of flexibility but may come at a performance cost.   Be aware the Formula is processed for every data point when loaded.   If you are viewing 90 days of data, the user has to wait for the formula to be run 90 times before the data is returned.   This only takes microseconds with simple calculation but if you performed some more complex logic, it could create a bad user experience.   For instance, if your script took 1 second to run, it will now take 90 seconds for the user to see the data which is obviously creating a bad user experience.

Formula Indicators vs. Performance Analytics Scripts

There is some overlap between what a Performance Analytics Script can do and a Performance Analytics Formula Indicators.   The main difference is that a Performance Analytics Script is run at collection time and the results are stored in the database.   PA Formula Indicators are calculated at display time and the results are not materialized.

Examples

And now let's get to the fun stuff…   Here are some examples of things you can do.   Regardless of the logic in your formula, just like a Performance Analytics Script, the last line of your Formula must contain (or evaluate to) the numeric result value for the Formula.

Before executing any script you should thoroughly test it in your environment.   Scripts are very powerful and you must ensure that you understand what you are running before you run it.   The scripts below are intended for discussion purposes only.

Round to a Specific Digit of Precision

Performance Analytics lets you set the precision for an indicator which is what you need in most cases, but what about the use case where you want to round to the nearest quarter of a point.   In this example, we use the standard Math JavaScript library to round with our specific rules.

// Round to the nearest quarter point
var number = [[My Indicator]];
Math.round(number * 4) / 4).toFixed(2);

Show a Constant Value

If I need to show a constant value, I can create a manual indicator and enter the values for each date.   Another option is to create a formula with the constant in it.   Here I'm setting the constant value.

{{Some Non-Null Indicator}}
50;

You may ask why there is an indicator above the constant that doesn't appear to be used.   This is required because Formula Indicators are optimized to not be processed if there are no non-null indicators passed.   This ensures that there is always a non-null value so that the formula is processed and we get the desired result (constant value "50" in this case).

Show Only Future Values

Some indicators cannot be accurately forecasted based on historical data.   In this case, a manual indicator is often used to allow us to see the "forecast" line with the actual lines.   For this case, we if we don't want to show the historical manual forecast, we have to go in and manually delete.   If we don't want to do that (I certainly don't), we can use date comparisons to ensure we only show the manual indicator for future dates.

if(score_end.compareTo(new GlideDateTime()) >= 0)
{
    [[My Manual Incident Forecast]]
}

If you aren't familiar with GlideDateTime and the compareTo method, you can find out more about it here.

Here is a little reference that I keep to remind me what I am looking for.   [NOTE: This is valid ServiceNow JavaScript, but NOT a valid Formula Indicator.]

// Setup the variables that PA supply to formulas
var score_start = new GlideDateTime('2018-01-01');
var score_end = new GlideDateTime('2018-01-31');

// Identify what I'm actually comparing
var compareStart = score_start.compareTo(new GlideDateTime());
var compareEnd = score_end.compareTo(new GlideDateTime());

// Show make sure I pick the right result
if(compareStart <= 0 && compareEnd > 0)
{
  gs.addInfoMessage('In Period');
} else if(compareEnd <= 0) {
  gs.addInfoMessage('After End');
} else if(compareStart > 0) {
  gs.addInfoMessage('Before Start');
}

Use a Different Indicator or Breakdown Depending on Date

In this example, we are using a different breakdown depending on the day of the week, but this could easily be modified to combine an indicator from a previous period and a new period (perhaps due to some process change).

var result = null;
switch(score_start.getDayOfWeekUTC())
{
case 1:
  result = [[Avg. Incidents > Day of Week = Monday]];
  break;
case 2:
  result = [[Avg. Incidents > Day of Week = Tuesday]];
  break;
case 3:
  result = [[Avg. Incidents > Day of Week = Wednesday]];
  break;
case 4:
  result = [[Avg. Incidents > Day of Week = Thursday]];
  break;
case 5:
  result = [[Avg. Incidents > Day of Week = Friday]];
  break;
case 6:
  result = [[Avg. Incidents > Day of Week = Saturday]];
  break;
case 7:
  result = [[Avg. Incidents > Day of Week = Sunday]];
  break;
}
result;

Query Other Data to Transform Data

In this example, we query to get an exchange rate so we can display values in EUR even though the scores are in USD.   If we needed to have scores in a dozen different currencies, this would allow us to be able to support that without the need to store those scores.   This comes at a runtime performance cost so while we can do this, it is not always the best solution.

This could be very useful if we had a user preference for currency, this could be used to dynamically transform the USD scores to the user's local currency (as long as we had an FX rate for it).

var rate = null;
var gr = new GlideRecord('fx_rate'); // fx_rate is EUR based
gr.addQuery('currency.code', 'USD');
gr.addQuery('sys_created_on', '<=', score_end); // get the rate in effect at the end of the period
gr.orderByDesc('sys_created_on'); // fx rates are loaded daily and we want the most current
gr.setLimit(1); // only get one value, the latestet
gr.query();
if(gr.next())
{
      rate = gr.getValue('rate');
}
[[USD Sample Data]] / rate; // Evaluate the USD value divided by the FX Rate

Conclusion

The NOW Platform gives Performance Analytics Formula Indicators tremendous power and gives you an amazing amount of flexibility to best meet your users' needs.   While you need to use them responsibly, Formula Indicators are a powerful tool you should keep ready in your Analytics Tool Box.