Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MDX CurrentMamber.Lag - how to force hierarchy level used

Posted on 2010-11-23
1
Medium Priority
?
2,264 Views
Last Modified: 2016-02-15
How do I force the MDX CurrentMember.Lag(N)  method to operate at a particular level of the time hierarchy? I am creating a sales report that mixes weekly figures, monthly figures and yearly figures.  I would like to use the CurrentMember.Lag method to look back a given set of weeks or months for comparison purposes. I think the approach to take is to use is something like this (using AdventureWorks data):

Create member [Measures].[Month Ten Weeks Ago] AS
(
[Date].[Calendar].CurrentMember.Lag(10):[Date].[Calendar]CurrentMember.Lag(7),
[Measures].[Reseller Sales Amount]
)

Or

Create member [Measures].[The Months Ago] AS
(
[Date].[Calendar].CurrentMember.Lag(3),
[Measures].[Reseller Sales Amount]
)

But the report will be filtered for a particular date, so I suspect that these lag methods will be looking back in days rather than weeks or months. Is there a way to force them to use the Time hierarchy level of my choice?
0
Comment
Question by:LinksGu2
1 Comment
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 1000 total points
ID: 34201539
If CurrentMember is at your Date level you could use CurrentMember.Parent to get to the current week and Currentmember.Parent.Parent to get to the month (assuming Month->Week->Date hierarchy).

Also look at the Ancestor function which might be of use to you.

Tim
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question