# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Creator III

## How to calculate MAT with two dimensions

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

1 Solution

Accepted Solutions
Highlighted
MVP

Try this

22 Replies
Highlighted
MVP

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?

Highlighted
Creator III

yeah sure sunny just give me few minutes

Highlighted
MVP

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

Highlighted
Creator III

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

Highlighted
Creator III

Yeah Sunny Exactly

Output values in numbers or may be decimals

Highlighted
MVP

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

Highlighted
Creator III

Actually we solve  these kind of queries with one dimension

Highlighted
Creator III

have a great  day....

Highlighted
MVP

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)