Today’s post is from Eric Strovink of BIQ.

Sometimes you want and need control over how (and when) measures are calculated. Such measures are termed “user-defined” measures.

As we saw previously, there are two kinds of user-defined measures:
(1) post-facto computations that are performed after transactional roll-up (the usual definition), which we’ll consider here, and
(2) those that are performed during transactional roll-up, which were covered in Part 1.

In the above example the gray “Ref” columns are filtered on commodity count/spend in Q1 2003, and the normal column is filtered on commodity count/spend in Q1 2004. If we then additionally filter on four business units:

we can now see the quarter-on-quarter comparison for just those business units:

You can see that two filter operations are occuring every time the dataset is filtered; one for the regular filter above, and one for the reference filter, modified by non-conflicting filter operations. This “dynamic” reference filtering can be quite powerful, since the relationship between the two periods is now available at any filter position in the dataset.

Now, let’s add a post-rollup (“nodal”) computed measure that calculates the %difference between these columns. The code reads like this:

$%Diff$ = ($Amount$-$RefFilter4.Amount$)/$RefFilter4.Amount$ * 100;

Now, if we sort top down by %difference, we can see quite clearly the quarter-on-quarter difference sorted by worst to best, considering just the four cost centers above:

This percentage difference is available to all analysis modules, because it is calculated at every node, not just at the nodes that are currently being displayed.

Next installment: Meta Dimensions; but I’ll take a few weeks off before diving back in!

Previous Installment: User-Defined Measures, Part I

Share This on Linked In