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

Answer 1

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

Thanks

Mvino

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Hello Challen Fu,

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

Thanks

Mvino