Calculate a metric for a rolling period

Products

• CA Business Service Insight

Issue/Introduction

How to setup a metric within a contract that will provide a rolling 3 month performance calculation every month?

Each month a result should be calculated using data for the previous 3 months.  For example, in November the result should calculate using data for September, October and November and in December the result should calculate using data for October, November and December.

Environment

CA Business Service Insight 8.3.x and 9.x

Resolution

One way to accomplish this would be to setup a metric with a period of 3 months and granularity of 1 month. This is not really “rolling” in itself.  In this simple example the metric would look something like this:

 

This is the easiest way to set this up, but there is a huge downside. In this example the period start would be the start of the contract effective date and the period end would be every three months. In other words, if the contract started on January then the period would end (and return the full result) in March.  This does not exactly accomplish the requirement unless you setup three metrics which each start a month later than each other. So you could have three metrics with a 3 month period which start January 1^st, February 1^st and March 1^st. While this is easy, it is not the ideal solution as you now have three metrics to report on.

The best way this could be done would be through a business logic script and by using slalom.map and the T_SLALOM_OUTPUT table.  

This approach lets you save any custom data to this user table (T_SLALOM_OUT) and then use it in calculations later.
 
1.  Setup a metric with a period of 1 month since we want to get this calculation every month.
 
2.  Create a slalom.map. For example:

Sub OnLoad(TIME)
Set myEvents = CreateObject("SlalomMap.Map")
End Sub
 
3.  Have the business logic script save the pertinent data for each event to the slalom map in a way that makes it convenient to pull the last three months data into the calculations from the OnPeriodEnd function.

For example, assuming you wanted an average over 3 months, then in the OnEvent function (or whatever function you have setup to run for each event) do something like:

iTime = formatdatetime(eventDetails.Time)//this is custom function that could be
//created to format the date/time from the event to something
//easy to compare. This will be used for the index/identifier
If Not myEvents.exist(iTime) Then//if an event with that timestamp
//does not already exist.
myEvents.item(iTime) = eventDetails.Time &"|" & eventDetails("Data")
               //in this example, I have a simple event type with Time and Data. This would be
//what we are eventually averaging. But you can do anything with this.

4.  Make the calculations OnPeriodEnd for the past three months by pulling the data from the slalom map. Assuming the myDate field in the slalom.map is formatted as YYYYMM, then we could simply say something like:

For Each element In myEvents
itemarray = split(element,"|")    //We saved data and time together so this splits it out.
If itemarray(1) +3 < currentMonth Then   //currentMonth would be some variable we
//setup with the current date/time in YYYYMM format.
                              myEvents.erase(element)              //Here we cleanup events older than 3 months.
                              Else
                                             Count++
                                             Total = Total+ itemarray(2)
                              End If
               Result = Total / count                                   //to get the average.
Return Result
 
The above example is at a high level and there are other things to consider (i.e. date formatting and initializing each variable etc).