MDX: Linkmember > Descendants offsets with currentmember context

Question

I'm self taught in MDX and am struggling with a problem.

I need to be able to isolate a set of products according to a window around their release date and then calculate their sales by the current rows date context. The 'window' is a set of streetdates where member is between or equal to currentmember -27 and currentmember + 7 (as we sometimes see early sales/preshipping).

Final output should look something like (forward days blank):

            NR Sales
2018-03-25  117.12
2018-03-26  130.01
2018-03-27  2,827.63
2018-03-28  
2018-03-29  
2018-03-30  
2018-03-31

My cube has a street/release date hierarchy which is a mirror of my fiscal date hierarchy. It pulls correct values with a slightly altered query looking at a single day in the where clause.

The below query returns values, but is slightly incorrect as i think the descendants call isn't working properly.

WITH 
SET [StreetDateSet] AS 
  LinkMember
  (
    Descendants
    (
      [Date].[Yr-Qtr-Mo-Wk-Day].CurrentMember
     ,[Date].[Yr-Qtr-Mo-Wk-Day].[Day]
    ).Item(0)
   ,[Street Date].[StreetDate-Yr-Mo-Wk-Dy]
  ).Lag(27)
: 
  LinkMember
  (
    Descendants
    (
      [Date].[Yr-Qtr-Mo-Wk-Day].CurrentMember
     ,[Date].[Yr-Qtr-Mo-Wk-Day].[Day]
    ).Item(0)
   ,[Street Date].[StreetDate-Yr-Mo-Wk-Dy]
  ).Lag(-7) 
MEMBER [NR Sales] AS 
Sum
(
  [StreetDateSet]
 ,[Measures].[Sales Value]
) 
SELECT 
  NON EMPTY 
[NR Sales] ON 0
,[Date].[Date].Children ON 1
FROM [CUBE]
WHERE 
(
[Date].[Yr-Qtr-Mo-Wk-Day].[Week].&[2018]&[2]&[4]
,[Item].[New Used].[New]
,{
  [Item].[Category Code].[220]
 ,[Item].[Category Code].[230]
 ,[Item].[Category Code].[210]
}
);

I suspect there's a few problems here but am not sure how to address them. Descendants is likely impacted by the date hierarchy in the where clause. I've tried to get it into the 1 axis, but i get 'The Yr-Qtr-Mo-Wk-Day hierarchy already appears in the Axis1 axis.' and am unsure how to address.

Thanks for any advice/guidance on where i'm going wrong.

Peevo Peevo · Accepted Answer · 2018-04-04T04:53:34

Turns out that linkmember wasn't required and ended up finding a solution via MSDN that uses a filter/Properties function:

Set iteration in calculation

Solution involves a member which iterates a NULL * CurrentMember defined set and I slightly adopted the statement to generate a dynamic set which matches off the complementary hierarchies.

WITH 
MEMBER [Measures].[SameDay] AS 
  NULL * Count([Date].[Yr-Mo-Wk-Day].CurrentMember AS MySet)
+ 
  Sum
  (
    {
        Filter
        (
          [Street Date].[StreetDate-Yr-Mo-Wk-Dy].MEMBERS AS MySet2
         ,
          MySet2.Current.Properties("Key") = MySet.Item(0).Properties("Key")
        ).Item(0).Lag(28)
      : 
        Filter
        (
          [Street Date].[StreetDate-Yr-Mo-Wk-Dy].MEMBERS AS MySet2
         ,
          MySet2.Current.Properties("Key") = MySet.Item(0).Properties("Key")
        ).Item(0).Lead(7)
    }
   ,[Measures].[Sales Value]
  )

MDX: Linkmember > Descendants offsets with currentmember context

1 Answers