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?
