Solved

MDX CurrentMamber.Lag - how to force hierarchy level used

Posted on 2010-11-23
1
2,026 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 250 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

685 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