Filtering a Dimension Relative to a CurrentMember in MDX

1
votes

I'm having a bit of trouble accomplishing something that I think should be relatively straightforward in MDX. I would like to create a calculated member that provides a sum of one of my measures over the previous two weeks at a given point in time. My time dimension looks like:

TimeId    TradingDate   Day of Week
-----------------------------------
1000      11/1/2012     Thursday
1001      11/2/2012     Friday
1002      11/5/2012     Monday
1003      11/6/2012     Tuesday
...       ...

What makes this particularly difficult is that my Time dimension is not quite complete. The members of my Time dimension only correspond to trading days in the stock market, and not all time. This means that weekends, holidays, or any other day in which the stock market is closed are excluded. This also means the normal methods of traversing time such as LAG or PARALLELPERIOD will not work quite right here. LAG(14), for example, means "14 trading days", which at any given point could represent a variable length of actual time.

Inside my calculated member, I'm attempting to use FILTER in order to get only time members that are within the previous two weeks of the CurrentMember. However, I can't seem to figure out the proper syntax (if there is one) to accomplish this. I imagine it would be something like:

WITH MEMBER [Sum of Price Previous 2 Weeks] AS
    SUM(
        FILTER(
            [Time].[TimeId].Children
            , [Time].[TradingDate].MemberValue 
              >= VBA!DATEADD("ww", -2, [Time].[TradingDate].CurrentMember.MemberValue)
        )
        , [Price]
    )

However, this doesn't quite work. I can't seem to separate the context of the calculated members current iteration from what would be a separate context inside of the FILTER function. In other words, I'm not sure how to say:

"When iterating over the set inside of FILTER, compare the current member of each iteration against the value of the CurrentMember in the scope of the calculated member"

Is what I'm trying to accomplish even possible? Is there a different approach I could be taking to accomplish what I'm after?

2
ssasmdxolapssas-2008
Is your time dimension set up properly in SSAS as a time dimension, and have you mapped the different dimension attributes to their corresponding time component? (Doing so will help in using the time functions available in MDX) – cairnz
The Time dimension is set up as a "Time" dimension in SSAS, and the type of TradingDate is Date. However, TimeId is only set to Regular as I wasn't sure it could be considered a date. It does correspond to a particular date though. It is also important to note that my Time dimension is by no means a complete representation of time. It only represents trading days in which the stock market is open. – mclark1129
I have had this problem repeatedly. CurrentMember does refer to "this iteration" when the dimension is not used on an axis otherwise. But the more general question I hope to answer is "how can I filter a dimension on its own attributes." – Bill
@Bill I hope you can answer it too, it's driving me nuts! :) – mclark1129

2 Answers

2
votes

The result you'll get from a calculated member will depend on the axis of your query. So first, make sure you have [Time].[TradingDate] in your axis.

Second, your [Time].[TradingDate] hierarchy should be ordered by Key (I assume TradingDate is the key).

Now you can use this member definition:

WITH MEMBER [Sum of Price Previous 2 Weeks] AS
    SUM(
        [Time].[TradingDate].CurrentMember.Lag(14):[Time].[TradingDate].CurrentMember, [Price]
    )
2
votes

You can use set aliases to refer to the outer CurrentMember in the Filter context:

WITH MEMBER [Sum of Price Previous 2 Weeks] AS
        SUM(
            GENERATE([Time].[TradingDate].CurrentMember AS CurrentDateAlias,
                     FILTER(
                          [Time].[TimeId].Children
                          , [Time].[TradingDate].MemberValue 
                          >= VBA!DATEADD("ww", -2, CurrentDateAlias.Item(0).MemberValue)
                     )
            )
            , [Price]
        )

GENERATE is used just to define the alias somewhere.