SYMPTOM:
A user wishes to create a report such as the following, which includes a derived metric using the MovingDifference function. The purpose of the moving difference metric is to calculate the delta between values of the source metric (Revenue) for successive quarters. That is, for Books, the moving difference value for Q2 2004, 4887, is the result of Q2 2004 Revenue (9858) - Q1 2004 Revenue (4971), as shown below:
The derived metric is defined as follows. Note the break by and sort by parameters: the function is to break the calculation based on the row attribute, and sort by the column attribute:
When a custom group is substituted in place of the category attribute, the results of the moving difference metric are incorrect, as shown below:
The same behavior will occur if a consolidation is used in place of the custom group.
CAUSE:
This behavior is a result of a known issue in Strategy Analytical Engine 9.x
Strategy OLAP functions such as RunningSum depend on correct sorting and grouping (break-by) properties for the metric results to make sense. While these properties may be specified in derived metrics, the sorting and grouping are not evaluated correctly against custom groups and consolidations.
All of the built-in Strategy OLAP functions will be affected:
ACTION:
This issue is fixed in Strategy 10.x. Upgrade to it to take advantage of the issue correction.
WORKAROUND:
If the derived metric is rebuilt as a standalone metric, the OLAP calculation will be successful, as shown below:
For this report, the "MovingDiff standalone" metric is defined as follows:
KB14183
