My first attempt was to utilize the Lag function as I had in many other formuals, but found that I kept getting results of zero. Using the MDX Script Editor in EAS, I found that the Lag function was only valid within the hiearchy that the specified member was found in. After reviewing the list of available functions, I took a look at the COUSIN function. I found it was exactly what I needed.
Here is an example of how it might be utilized:
Let's assume fort his example that you have two hiearchies within your period dimension. Let's also assume that data is loaded on a weekly basis. The first hiearchy provides your weeks, months, quarter, and year. Your alternate hiearchy is a dynamic calc of the quarter to date as of each week. So your two hiearchies look like this:
For each member you could do a unique formula, specific to each week:
- WK1 = [Wk1]
- WK2 = Sum(MemberRange([WK1],[WK2])
- WK3 = Sum(MemberRange([WK1],[WK3])
- WK4 = Sum(MemberRange([WK1],[WK4])
- WK5 = Sum(MemberRange([WK1],[WK5])
- WK1 thru WK13 = Sum(MemberRange([WK1],Cousin([Period].CurrentMember,[Year])))
- WK14 thru WK26 = Sum(MemberRange([WK14],Cousin([Period].CurrentMember,[Year])))
- WK27 thru WK39 = Sum(MemberRange([WK27],Cousin([Period].CurrentMember,[Year])))
- WK40 thru WK52 = Sum(MemberRange([WK40],Cousin([Period].CurrentMember,[Year])))
It could be taken the next step so that the same formual is use for each WK QTD member:
- Sum(MemberRange([Period].CurrentMember.Parent.Parent.FirstChild.FirstChild,Cousin([Period].CurrentMember,[Year])))
Please note there are probably several different ways that you can set your the first member of your member range to by dynamic. This is probably a rather crude way, but since this post is about the COUSIN function, I went with the simplest route that would validate.
In summary, the COUSIN function allows the flexibility reference a child member at the same relative position as a member from another ancestor. This can be used within the same member hiearchy or across another member hiearchy.
