Calculate a metric for a rolling period

Document ID : KB000124471
Last Modified Date : 14/01/2019
Show Technical Document Details
Question:
I would Like to setup a metric within a contract that will provide a rolling 3 month performance calculation every month. To Be clear - each month a result should be calculated using data for the previous 3 months (November should calculate using data for Sep, Oct, and Nov; December should calculate using data for Oct, Nov, and Dec.). How can this be done?
Answer:
This can be done in two ways I can think of.
  1. The first way we could 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 (which I will discuss below). So, in this simple example our metric would look something like this:
metric
 
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, then the next period would be June, etc. This does not exactly accomplish what is described above 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 1st, February 1st, and March 1st. While this is easy, it’s not the best solution. For one thing, you now have three metrics to report on.
So how can this be done better?
  1. The best way this could be done (though it requires some more work) would be through your business logic script and by using slalom.map and the T_SLALOM_OUTPUT table.  
This lets you save any custom data you want to this user table T_SLALOM_OUT and then use it in your calculations later.
 
First, we would setup a metric with a period of 1 month since we want to get this calculation every month.
 
Second, you would create a slalom.map. For example:
Sub OnLoad(TIME)
Set myEvents = CreateObject("SlalomMap.Map")
End Sub
 
Third, 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 in your calculations from the OnPeriodEnd function. For example, assuming I 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.
Fourth, you would make your calculations OnPeriodEnd for the past three months by pulling the data from the slalom map. I don’t want to get into all the intricacies of date formatting in visual basic since this can get very involved, so assuming we saved a myDate field in the map formatted as YYYYMM (understanding that date formatting is always a bit more complicated than this) 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
 
Now obviously this is an example where several things were glossed over. For one we did not really worry about date formatting and initializing each variable etc. However I hope that it demonstated the pertinent points for this which was to save the data you need to your map each time an event comes in, to calculate using the last three months data from your map in OnPeriodEnd, and to cleanup old data.