none
MAT and MQT in SSAS MDX

    Question

  • Hello,

    PLease can someone show me how to calculate MAT and MQT (Moving quarterly Totals). I have been asked to implement these two MDX calcualtions on my measure and I have no clue how to go about it.

    Your ideas and solutions are highly welcome.

    Thanks

    Mvino

    Monday, April 02, 2012 2:03 PM

All replies

  • I actually mean the steps needed to implement these calcualtions and the actual calculation itself. This is deeply appreciated.

    Thanks

    Mvino

    Monday, April 02, 2012 2:13 PM
  • Hi Mvino,

    Here you want to get the MAT (Moving Annual Total), you can use lastperiods function, I gave below Query sample based on Adventure Works database, you can get some reference from it, it is same to MQT.

    WITH 
     
    MEMBER measures.A AS
     
    sum(
     
            {
     
                lastperiods(
     
                    8,
     
                    [Date].[Calendar].currentmember
     
                )
     
            }
     
            ,
     
            [Measures].[Internet Sales Amount]
     
    )
     
    SELECT
     
    {[Measures].[Internet Sales Amount], measures.A} 
     
    ON 0,
    
    [Date].[Calendar].[Calendar Year].members on 1
    FROM
     
    [Adventure Works];

    For more information about this topic please see:

    http://sqlblog.com/blogs/mosha/archive/2007/09/04/moving-averages-in-mdx.aspx
    http://wikiprogrammer.wordpress.com/2011/02/23/mat-moving-annual-total-ytd-year-to-date-calculation-using-bottomcount-tail-properties-function-in-mdx/


    Challen Fu

    TechNet Community Support


    • Edited by Challen FuModerator Friday, April 06, 2012 2:58 AM
    • Proposed as answer by Raunak J Friday, April 06, 2012 5:05 AM
    • Marked as answer by Challen FuModerator Tuesday, April 10, 2012 11:58 AM
    • Unmarked as answer by MVino Tuesday, April 10, 2012 1:07 PM
    Friday, April 06, 2012 2:57 AM
    Moderator
  • Hello Challen,

    I have a question. I have studied the solution you gave me and am bothered about

              

    sum(

           
    {

                lastperiods
    (

                   
    8,

                   
    [Date].[Calendar].currentmember

               
    )

           
    }
    Did you mean "8" here or it was an oversight? I thought it will be "12" since we are calculating for a year - pls correct me if I am wrong.

    Thanks

    Mvino

    Tuesday, April 10, 2012 12:22 PM
  • Yes, you are right, 8 is just a sample for you. You can change it to 12 based on your requirement.

    Challen Fu

    TechNet Community Support

    Wednesday, April 11, 2012 1:20 AM
    Moderator
  • Hi Mvino,

    What's the process? If you have any other question, please feel free to let us know.

    Thanks,


    Challen Fu

    TechNet Community Support

    Thursday, April 12, 2012 2:19 PM
    Moderator
  • Hello Challen Fu,

    I am still stuck with the MQT thing.It seems not to work with the the above.

    Thanks

    Mvino

    Thursday, April 19, 2012 3:44 PM