Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX - Cube Calculated member sliding 12 month comparison with previous month data Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 10:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 6:22 AM
Points: 574, Visits: 800
Hi,

I want to compare Aggregate of following two different resultsets:


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON Empty

{
LastPeriods(
12, [Date].[Calendar].[Month].[January 2008]
)
}
On Rows
FROM
[Adventure Works];
GO



SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON Empty

{
LastPeriods(
12, [Date].[Calendar].[Month].[February 2008]
)
}
On Rows
FROM
[Adventure Works];
GO




Now actually i want to add a calculated member in the cube in a way that in Current Month, it compares aggregated sales of previous 12 months with the aggregate of previous 12 month from one month before previous month.

For example

In march 2008
Aggregate(Mar2007 .... Feb2008) ,
Aggregate(Feb2007 .... Jan2008) ,
Aggregate(Mar2007 .... Feb2008) - Aggregate(Feb2007 .... Jan2008) AS Rolling_12Month_Growth.

Note: it is different from Month-Over-Month Growth.

So any clue please?

Thanks.
Post #1341400
Posted Wednesday, August 8, 2012 1:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 6:22 AM
Points: 574, Visits: 800
Hi Moderator,

This is my second MDX question in "Analysis Services" forum of SSC and NO answer at all.

Am i on the right place for such questions? or what else?


Thanks.
Post #1341700
Posted Wednesday, August 8, 2012 7:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 6:22 AM
Points: 574, Visits: 800
The best help i found on this question is available on following Link. Thanks to Pavel Pawlowski. Excellent work.

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1d00b9fd-80f9-493c-8d04-b125882a6e5c/#1d00b9fd-80f9-493c-8d04-b125882a6e5c

Thanks and Cheers.
Post #1341870
Posted Wednesday, August 8, 2012 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:04 AM
Points: 33,095, Visits: 15,203
This is the right place, but we don't have a lot of MDX posters, so there might not be anyone that can easily answer.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1342052
Posted Wednesday, August 8, 2012 10:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:26 PM
Points: 2,926, Visits: 2,529
I think that the following will give you what you are after

WITH MEMBER Measures.CurrentMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(11) : [Date].[Calendar].Currentmember, [Measures].[Internet Sales Amount])'
MEMBER Measures.PriorMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(12) : [Date].[Calendar].Currentmember.lag(1), [Measures].[Internet Sales Amount])'
Member Measures.Difference AS 'Measures.CurrentMonth - Measures.PriorMonth'
SELECT
{measures.currentmonth, measures.priormonth, Measures.Difference} ON COLUMNS

FROM
[Adventure Works]
where ([Date].[Calendar].[Month].[February 2004])



Post #1342378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse