MDX Help

Hello,

I have the following MDX Complication.

Goal - Return on the members UP TO the current day of the week. ie Today is Wednesday. I want to return all members Monday-Wednesday.

I currently have a Time.Test.[Current Day of Week] to find the current Day of Week tuple.

Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

Below is my [Previous Week] tuple. Is there a way to limit the member set to only include members up to the current day of the week?

Tail(Filter([Time].[Week].Members,NOT IsEmpty(Time.Week.CurrentMember)),1).Item(0).Lag(1)

Any help is much appreciated!

-Lawrence

If I understand your hierarchies correctly, you should be able to do something like the following to get a set of members from the start of the week to the last nonempty day of the week.

Code Snippet

PeriodsToDate(

[Time].[Week]

, Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

)

I am also wondering if the calc might not be a bit faster if you replaced the filter test with the NonEmpty function

Code Snippet

PeriodsToDate( [Time].[Week], Tail(NonEmpty([Time].test.[day In week].Members),1).Item(0))

It's not clear if you wanted to do the same sort of thing to the previous week calc, but you should be able to use similar logic

|||

Hello,

I am not sure why the below MDX always return Sunday (7) instead of the current "Day In Week"?

The hierarchy has three levels [Year], [Day In Week] & [Day]. [Day In Week] is not the lowest level, so wondering if that's causing it

[Day In Week] has members (1,2,3,4,5,6,7) and Day follows

TAIL([Time].[Test].[Day In Week].Members, 1).Item(0)

TAIL(NONEMPTY([Time].[Test].[Day In Week].Members, [Measures].[Checking Account]), 1).Item(0)

Goal - return the current Day In Week

ie [Current Day] =

TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 4).Item(0)

ie [Current Day Of Week] =

Your help is much appreciated,

-Lawrence

|||

If your [Day In Week] attribute only has 7 members then if you have any Sunday with data then the Tail(NonEmpty()) expression will always return [Sunday].

Assuming that your definition of "current date" is the last date with data, then you will probably need to use a date attribute (one that has a member for each date) and find the last nonempty member in that attribute and then use the EXISTING operator to filter that member against the [Day In Week] attribute.

eg

EXISTING Time.[Test].[Day In Week].Members, {[Time].[date].[Date].[<Current Date>]}

|||

My [Current Date] returns the last Day member that has data.

[Current Date] = TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)

You are saying to use this member to find the [Day In Week] member.

I tried the code below, and it returned the correct [Day In Week] member.

select EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} ) on columns from gmd

So I used the following

[Current Day of Week] = EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} )

However, I am getting an error (#Error) when I test [Current Day of Week]

select [time].[test].[current day of week] on columns from gmd

select ([Checking Account], [time].[test].[current day of week]) on columns from gmd

Thanks for your help again,

-Lawrence

|||

Sorry, my mistake. I got Existing and Exists() round the wrong way. Try using Exists().

PS. You should be able to hover over the #Error or double click on it in SSMS to see a more detailed error message. I'm guessing it would have been saying that the Existing operator requires a single set as input.