topic Re: How to calculate MAT when year as a dimension in QlikView

How to calculate MAT when year as a dimension

hemanthaanichet — Tue, 24 Jan 2017 04:36:54 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Tue, 24 Jan 2017 04:41:32 GMT

Sorry Guys

I attached a worng QVW

Pls have a look on the corrected attachment below

Regards

Hemanth

Re: How to calculate MAT when year as a dimension

sunny_talwar — Tue, 24 Jan 2017 10:43:31 GMT

May be this?

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

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Tue, 24 Jan 2017 11:23:19 GMT

Hi Sunny,

Exactly the same answer which i required.

But still one more required when i select the year the data is not displayed in the chart

please sort out this one

Re: How to calculate MAT when year as a dimension

sunny_talwar — Tue, 24 Jan 2017 12:06:31 GMT

Ignore selection in Year field my friend. Have you not looked at set analysis guide yet? Highly recommend spending some time with it -> Set Analysis: syntaxes, examples

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

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Tue, 24 Jan 2017 12:18:17 GMT

Oops i forget it, Highly stressed in mind might forget small small things

But these forget things can be over come when there is a friend like you.

Cheers....!

Re: How to calculate MAT when year as a dimension

sunny_talwar — Tue, 24 Jan 2017 12:19:31 GMT

You got it brother

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Wed, 25 Jan 2017 06:05:30 GMT

Hi Sunny,

If u dont mine can please explain the expression a bit clear

actually what happened is the expression is working very fine with my sample data

But when i implemented the same expression in my original data it is not working

even i tired step by step too

firstsortedvalue function is not working in the original data

can u pls help me

Regards

Hemanth

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Wed, 25 Jan 2017 11:21:09 GMT

Hi Sunny,

Actually what is issue is

In the sample data , For one month there is only one value

ex:

YearMonth Sale

Apr-16 201

May-16 301

But I my actual data, for one month there are so many values

Ex:

Yearmonth Sale

Apr-16 201

Apr-16 202

Apr-16 203

Apr-16 204

May-16 301

May-16 302

May-16 303

May-16 304

May-16 305

i think because of this the firstsortedvalue function hasnt work

is there any other suggestion to overcome this issue

Regards

Hemanth

Re: How to calculate MAT when year as a dimension

sunny_talwar — Wed, 25 Jan 2017 11:57:36 GMT

Would you be able to update the sample and show me the issue?

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Wed, 25 Jan 2017 12:04:19 GMT

Hi Sunny,

Attached the sample data with few updates

Re: How to calculate MAT when year as a dimension

sunny_talwar — Wed, 25 Jan 2017 12:10:06 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Wed, 25 Jan 2017 12:39:01 GMT

Hi Sunny,

Your Expression works fine in the sample data

but i implemented in my original data values are not correct

Re: How to calculate MAT when year as a dimension

sunny_talwar — Wed, 25 Jan 2017 12:40:17 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Thu, 26 Jan 2017 08:24:20 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Fri, 27 Jan 2017 05:31:08 GMT

Hi Sunny,

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

Re: How to calculate MAT when year as a dimension

sunny_talwar — Sat, 28 Jan 2017 21:41:22 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Sun, 29 Jan 2017 08:09:50 GMT

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?

Re: How to calculate MAT when year as a dimension

sunny_talwar — Sun, 29 Jan 2017 12:16:06 GMT

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

Re: How to calculate MAT when year as a dimension

hemanthaanichet — Mon, 30 Jan 2017 10:22:06 GMT

Thank you Sunny..

Feeling very happy...

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

Cheers....!