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

22 Replies
hemanthaanichet
Creator III
Creator III
Author

Hi Sunny..

Apologies for the delay as like before i was a bit confused in the requirement there is a change in the calculation


Formula:

(Current Year MAT Sale /  Current Year  MAT Total Sale)

-

(Previous Year MAT Sale / Previous Year  MAT  Total Sale)

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

sunny_talwar

Would you be able to do this calculation manually for one of the brands and tell me what is the number that you expect to see? That will be huge help

hemanthaanichet
Creator III
Creator III
Author

Sure sunny... Iam dng the same

hemanthaanichet
Creator III
Creator III
Author

i check for Brand = ABC

Current Year MAT Sale  = 6111

Current Year  MAT Total Sale = 39174

Previous Year MAT Sale  = 3831

Previous Year  MAT  Total Sale = 35427

As per formula output result = 0.0478584631

hemanthaanichet
Creator III
Creator III
Author

Any clue r suggestion stalwar1

i think we have to use period type as also.....

sunny_talwar

Try this

Capture.PNG

hemanthaanichet
Creator III
Creator III
Author

output of the result is correct , But one more request i need to show all the years kindly resolve it ...

sunny_talwar

Like this

Capture.PNG

hemanthaanichet
Creator III
Creator III
Author

like that only, i tired with <= in the year .

it is showing only two years but in the year field we have from 2010 to 2016

when i select the year=2014  no data to display pls check it once...

sunny_talwar

Your AsOfPeriod chart above that also shows 2015 data. I am not sure if you have data in your application bro. Was this a sample which included only partial data?