Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hemanthaanichet
Creator III
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
sunny_talwar

22 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Actually we solve  these kind of queries with one dimension

below is the thread

How to calculate MAT when year as a dimension

hemanthaanichet
Creator III
Creator III
Author

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)