Solved: Re: How to calculate MAT when year as a dimension - Page 2 - Qlik Community

hemanthaanichet · ‎2017-01-23

Hi Guys,

I need to calculate MAT for the available data

Moving Average Turnover (MAT):

Dimensions: Year

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

hemanthaanichet · ‎2017-01-25

Hi Sunny,

Attached the sample data with few updates

sunny_talwar · ‎2017-01-25

Try this:

FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year>}Aggr(RangeSum(Above(Sum({<Year>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year>}YearMonth), YearMonth))

/

Above(FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year>}Aggr(RangeSum(Above(Sum({<Year>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year>}YearMonth), YearMonth)))-1

hemanthaanichet · ‎2017-01-25

Hi Sunny,

Your Expression works fine in the sample data

but i implemented in my original data values are not correct

sunny_talwar · ‎2017-01-25

What's the difference? Do you know I def. have no idea

hemanthaanichet · ‎2017-01-26

Hi Sunny,

Output Values are not matching when checked manually

when in expression output values

i.e.,

2013 = ((Sep-12 to Aug-13) - (Sep-11 to Aug-12)) / (Sep-11 to Aug-12)

2014 = ((Sep-13 to Aug-14) - (Sep-12 to Aug-13)) / (Sep-12 to Aug-13)

2015 = ((Sep-14 to Aug-15) - (Sep-13 to Aug-14)) / (Sep-13 to Aug-14)

2016 = ((Sep-15 to Aug-16) - (Sep-14 to Aug-15)) / (Sep-14 to Aug-15)

when i checked manually the output values

i.e.,

2013 = ((Sep-12 to Aug-13) - (Sep-11 to Aug-12)) / (Sep-11 to Aug-12)

2014 = ((Sep-13 to Aug-14) - (Sep-12 to Aug-13)) / (Sep-12 to Aug-13)

2015 = ((Sep-14 to Aug-15) - (Sep-13 to Aug-14)) / (Sep-13 to Aug-14)

2016 = ((Sep-15 to Aug-16) - (Sep-14 to Aug-15)) / (Sep-14 to Aug-15)

Below link contains th Original Data

QVD Link

Sample.qvd - Google Drive

QVW Link

Sample.qvw - Google Drive

hemanthaanichet · ‎2017-01-27

Hi Sunny,

This mis matching is because of aggr, is there any other way we can solve without using Aggr

sunny_talwar · ‎2017-01-28

You need to fix your load order (or not if you are using QV12).... I assumed that you don't have QV 12 and since you shared the qvw,have fixed the load order in the script like this

Table:

LOAD date(date#(DATAPERIOD,'YYYYMM'),'YYYYMM') AS Period,

NVALUE as Sales

FROM

[Sample.qvd]

(qvd);

Calendar:

LOAD DISTINCT Period,

Year(Period) AS Year,

Num(Month(Period)) AS MonthNum,

Month(Period) AS Month,

Date(MonthStart(Period),'MMM-YY') as YearMonth

Resident Table

Order By Period;

Basically, you need to load the YearMonth field in ascending order for the expression to work properly. Since you cannot use Order by in a qvd load, I created another table called Calendar which is basically loaded ordering by Period field.

Once I did this, the expression just needed to ignore selection in Month field and it worked

FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year, Month>}Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year, Month>}YearMonth), YearMonth))

/

Above(FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year, Month>}Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year, Month>}YearMonth), YearMonth)))-1

Finally, apologize for a late response, just wasn't getting enough time to work on this.

Best,

Sunny

hemanthaanichet · ‎2017-01-29

Sunny..

Thank you very much. it working sunny once again than you very much

one clarification if i select year= 2014 the values are showing from 2015

is this because of our functionality of expression?

sunny_talwar · ‎2017-01-29

You want to see less then the years selected? May be try this:

FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year = {"$(='<=' & Max(Year))"}, Month>}Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year, Month>}YearMonth), YearMonth))

/

Above(FirstSortedValue({<MonthNum = {$(=Max({<Year = {$(=Max(Year))}>}MonthNum))}, Year, Month>}Aggr(RangeSum(Above(Sum({<Year, Month>}Sales), 0, 12)), YearMonth), -Aggr(Only({<Year, Month>}YearMonth), YearMonth)))-1

hemanthaanichet · ‎2017-01-30

Thank you Sunny..

Feeling very happy...

if u dnt mine if u have any QMC related doc pls can u share...

Cheers....!