none
SSAS Named Set Using [Dim Date].[START OF WEEK STARTING SUN DATE]

    Question

  • I'm trying to create a named set in my cube.  In my date dimension I've got the both [Date] and [Start of week starting sun date].  What I want to do is create a named set using [Start of week starting sun date] and have it give me the most recent 4 weeks.  So to recap, the data looks like this:

    DATE START OF WEEK   STARTING SUN DATE
    7/1/2016 6/26/2016
    7/2/2016 6/26/2016
    7/3/2016 3/3/2016
    7/4/2016 3/3/2016
    7/5/2016 3/3/2016
    7/6/2016 3/3/2016
    7/7/2016 3/3/2016

    What I've tried to implement for the named set is as follows:

    {
    strtomember("[Dim Date].[START OF WEEK STARTING SUN DATE].&[" +
     cstr(year(now())) + "-" +
     Right('0'+ cstr(Month(dateadd('m',-1,now()))),2) + "-" +
     Right('0'+ cstr(Day(now())),2)+
    "T00:00:00]")
    :
    strtomember("[Dim Date].[START OF WEEK STARTING SUN DATE].&[" +
     cstr(year(now())) + "-" +
     Right('0'+ cstr(Month(now())),2) + "-" +
     Right('0'+ cstr(Day(now())),2)+
    "T00:00:00]")}
    This approach works when I use just [Dim Date].[Date] however when I substitute that with [Dim Date].[START OF WEEK STARTING SUN DATE] I start running into problems due to the fact that not every date exists in [Dim Date].[START OF WEEK STARTING SUN DATE].  My question is, how could I implement something like this?

    • Moved by Just KarlModerator Wednesday, August 3, 2016 1:31 PM Looking for the correct forum.
    Tuesday, August 2, 2016 1:27 PM

Answers

All replies