Report Portal

How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations

How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?

Here is query example

WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"

MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),
[Measures].[Internet Sales Amount]), Format_String = "### ### ###"

MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),
[Measures].[Internet Sales Amount]),Format_String = "### ### ###"

MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]


Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,
[Date].[Calendar].[Month].Members On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2004]);

Result will be:

 Internet Sales AmountInternetSalesAmtYTDInternetSalesAmtPPYTDInternetSalesAmtR12Acc
Jan-04$1,340,244.951 340 245438 86510 692 440
Feb-04$1,462,479.832 802 725927 95611 665 830
Mar-04$1,480,905.184 283 6301 413 53012 661 160
Apr-04$1,608,750.535 892 3801 919 93013 763 511
May-04$1,878,317.517 770 6982 482 70215 079 056
Jun-04$1,949,361.119 720 0593 037 50116 473 618
Jul-04$50,840.639 770 9003 924 17015 637 790
Aug-04(null)9 770 9004 771 58414 790 376

 

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.