How to accomplish (Date)Between in MDX?

Hi all,

I thought it would be simple, but it turned out to be to difficult for me. I have the next problem;

I want to get the salesinfo of the latest 30 days, It should show me all the data between _Now() minus 30 days and Now()_
Has anyone any Idea how this could be accomplished?

Regards.

The main trick here is how to map the result of the now() function to a particular member in the date dimension.

Could you tell us how your Date dimension is structured and give some examples of what you are using for the key and name for the day attribute.

Also, are you sure that you want to use now() - another common approach would be to find the last day with data and then go back 30 days. A lot of data warehouses get re-populated overnight, so they are always lagging atleast one day behind the live data.

|||

Thanks for your reply Darren,

My Time Dimension is structured as follow:
Year (1-1-2005, 1-1-2006)
Quarter (1-1-2006, 1-4-2006)
Month (1-1-2006, 1-2-2006)
Week (1-1-2006, 8-1-2006)
PKDate (1-1-2006, 2-1-2006)- this is the Key

The reason why I want to use Now() is that the Time Table in the Datamart has fixed dates until the year 2008. And I'm not able to change this because I'm only working on reporting, and we work with subscription which have an enddate in 2007 or 2008.

Hopely this clearifies it a little bit.

Regards.

|||

I'm still making some assumptions, but I think you could set up a calculated member roughly like the following.

Create member CurrentCube.Time.PKDate.Today as StrToMember("[Time].[PKDate].[" + format(now(),"d-m-yyyy") + "]")

Then your measure for the last 30 days would be:

Create member CurrentCube.measures.Last30Days as Aggregate([Time].[PKDate].Today:[Time].[PKDate].Today.lag(30))