Last week I posted about how to define rolling averages in Pentaho Analyzer. Since then, my colleague Andy Grohe pointed out that this could also be done via a trend measure rather than having to use MDX. I found this to be true with a minor caveat, but more on that a bit later. Andy’s suggestion is a great one because this method is far more accessible than getting into MDX.
Average Trend Measures
For now, let’s look at how to set up a trend measure to calculate averages based on prior periods. Let’s start with a simple example. In this data set, we’re filtering on March and April of 2013, and showing the numbered week of the year and a count of requests opened during those weeks:
Right clicking on the “Requests” dimension reveals a bevy of useful options, one of which is “User Defined Measures”, under which a “Trend Measure” can be defined. From here we can define our new “Average Requests” measure as follows:
Now we’ve got our new user defined measure included in our analysis, derived from the prior four time periods:
If we switch to a column-line combo chart, we get this view:
Right away you should notice something interesting: the average is based on the prior four time periods, so the line that is plotted is actually kind of a lagging indicator.
Rolling Time Periods
In an attempt to correct this “lag” issue, I thought I would leverage relative date filters in an attempt to force the average to be calculated on a rolling basis, including the weeks from the current and previous month, rather than explicitly filtering on March and April of 2013. For a more detailed discussion of how to set that up, please refer to my prior post on the topic.
After implementing the necessary changes to my schema, I leveraged the new filtering option (note the different filter selection in the filter pane), but the result was the same (still driven from the prior time periods):
I did also try filtering on current and previous 4 weeks (instead of months as above), but the behavior was the same.
This is where I think MDX becomes necessary, since you can use it to specify a formula the explicitly includes the current member as part of the range to be averaged for a calculated measure. For details on how to set that up, please refer to my prior post on the topic.
Using MDX you can achieve average calculations that are potentially as fine as the grain of your time hierarchy (which is very cool), but you may want to forecast expected future values as well. Such capabilities could really enhance decision making regarding resourcing, investment, retention, quality, or cost reduction. Pentaho does support such predictive analytics, but alas, that’s a post for another day. In the meantime, feel free to take Pentaho for a test drive.