# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
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

22 Replies
Creator III

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

MVP

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

Creator III

Sure sunny... Iam dng the same

Creator III

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

Creator III

Any clue r suggestion stalwar1

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

MVP

Try this

Creator III

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

MVP

Like this

Creator III

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...

MVP

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?

Tags