How to calculate MAT with two dimensions

[hemanthaanichet]

Hi Guys,

I need to calculate MAT for the available data

Moving Average Turnover (MAT):

Dimensions:

1. Year

2. Brand

Expression : (Current Year - Previous Year) / Previous Year

Current Year : latest 12 months i.e.,(Aug-15 to Jul-16)

Previous Year : previous latest 12 months i.e.,(Aug-14 to Jul-15)

Expression output value should be plotted in latest year i.e., 2016

Need to show all the Year

Example 1:

Dimension      Expression

2016           (Current Year (Aug-15 to Jul-16) - Previous Year (Aug-14 to Jul-15)) / Previous Year (Aug-14 to Jul-15))

2015           (Current Year (Aug-14 to Jul-15) - Previous Year (Aug-13 to Jul-14)) / Previous Year (Aug-13 to Jul-14))

sooo on for the remaining years

Month interval has to vary based on the selection

Exanple 2:

Selection: Year= 2016 Month= Apr

Dimension      Expression

2016           (Current Year (May-15 to Apr-16) - Previous Year (May-14 to Apr-15)) / Previous Year (May-14 to Apr-15))

2015           (Current Year (May-14 to Apr-15) - Previous Year (May-13 to Apr-14)) / Previous Year (May-13 to Apr-14))

sooo on for the remaining years

Selection: Year= 2015

Dimension      Expression

2015           (Current Year (Jan-15 to Dec-15) - Previous Year (Jan-14 to Dec-14)) / Previous Year (Jan-14 to Dec-14))

2014           (Current Year (Jan-14 to Dec-14) - Previous Year (Jan-13 to Dec-13)) / Previous Year (Jan-13 to Dec-13))

sooo on for the remaining years

Attached the same data

[sunny_talwar]

Hey -

Can you share you result in terms of numbers. I see you have AsOfPeriod in your Aggr() dimension, but used Year as dimension. Have you created AsOfYear also?

[hemanthaanichet]

yeah sure sunny just give me few minutes

[sunny_talwar]

I see that Year is what I wanted AsOfYear to be.... So I think we are good there. All we need to know is the output you are looking for in terms of numbers

[hemanthaanichet]

In the Qvw

i consider Year AsOf Year

but the values iam looking is

in the qvw there is month pivot table

that month table display latest 12 months sales against brands

now i need to show  sum of these 12 months sales in max year against brands

[hemanthaanichet]

Yeah Sunny Exactly

Output values in numbers or may be decimals

[sunny_talwar]

Leaving for work, will check it out once I reach office.

[hemanthaanichet]

Actually we solve  these kind of queries with one dimension

below is the thread

How to calculate MAT when year as a dimension

[hemanthaanichet]

Ok Sunny.. iam will wait for your reply

have a great  day....

[sunny_talwar]

May be this

Aggr(FirstSortedValue({<MonthNr = {$(=Max({<Year = {$(=Max(Year))}>}MonthNr))}, Year = {"$(='<=' & Max(Year))"}, Month>}

Aggr(rangesum(above(sum({<Year,Brand,Month>}Sales),0,12)), Brand, AsOfPeriod),

-Aggr(Only({<Year, Brand, Month>}AsOfPeriod), Brand, AsOfPeriod))

/

Above(FirstSortedValue({<MonthNr = {$(=Max({<Year = {$(=Max(Year))}>}MonthNr))}, Year = {"$(='<=' & Max(Year))"}, Month>}

Aggr(rangesum(above(sum({<Year,Brand,Month>}Sales),0,12)), Brand, AsOfPeriod),

-Aggr(Only({<Year, Brand, Month>}AsOfPeriod), Brand, AsOfPeriod)))-1, Brand, Year)