Tuesday, May 10, 2011

cousin() and parallelPeriod() functions

We recently had a discussion seeking clarity on the cousin() and parallelPeriod() functions. The expectation was that these functions should identify the same time period under a new parent. This is not how these functions are designed to operate. The intended behaviour is to locate the new member at the same relative position as the given reference member. Take the following hierarchy as an example:

The member index numbers are mentioned to the right of the period members to make this example easier to follow.

The expectation was that if we use 31/08/2007 as an input argument to the cousin or parallelPeriod function that the result will be 31/07/2007. However, these functions operate by finding the member in the same relative position. If you look at the index for 31/08/2007 you will see that it is the 22nd member beneath 08/2007. The functions will try to find the 22nd member beneath 07/2007. This results in an empty set because there are only 21 members beneath 07/2007.

A similar problem will exist if we select 02/08/2007. In this case the cousin and parallelPeriod functions will return 03/07/2007 because it is the member in the same relative position (the second member).

If a reliable method of finding the previous period is necessary, then it will be necessary to resolve the suppression which has been applied to the time dimension. All of the time periods must be included in the cube in order for the cousin and parallelPeriod functions to be able to accomplish what is required. This will ensure that 02/07/2007 remains in the second position instead of as the first child of 07/2007 as exists in the current cube.

There will be an additional change required for cases where the selected date member does not have a corresponding member under the new parent member. This will occur when selecting 30/03/2008 and going back one month to find the corresponding period in February of 2007. February will not have 30 members beneath it so the functions will not return any matching member. In this case it is possible to use the last member under February 2007 (28/02/2007) by extending the expression used in the report. The below expression can be used as a reference:

item(union(parallelPeriod(level(parent([setMonth])), 1, [setMonth]), lastSibling(parallelPeriod(level(parent([setMonth])), 1, firstSibling([setMonth])))),0)

1 comment: