topic Re: How to calculate MAT with two dimensions in QlikView

How to calculate MAT with two dimensions

hemanthaanichet — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to calculate MAT with two dimensions

sunny_talwar — Mon, 13 Feb 2017 10:17:02 GMT

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?

Re: How to calculate MAT with two dimensions

hemanthaanichet — Mon, 13 Feb 2017 10:18:40 GMT

yeah sure sunny just give me few minutes

Re: How to calculate MAT with two dimensions

sunny_talwar — Mon, 13 Feb 2017 10:20:32 GMT

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

Re: How to calculate MAT with two dimensions

hemanthaanichet — Mon, 13 Feb 2017 10:33:31 GMT

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

Re: How to calculate MAT with two dimensions

hemanthaanichet — Mon, 13 Feb 2017 10:35:37 GMT

Yeah Sunny Exactly

Output values in numbers or may be decimals

Re: How to calculate MAT with two dimensions

sunny_talwar — Mon, 13 Feb 2017 10:38:45 GMT

Leaving for work, will check it out once I reach office.

Re: How to calculate MAT with two dimensions

hemanthaanichet — Mon, 13 Feb 2017 10:40:17 GMT

Actually we solve these kind of queries with one dimension

below is the thread

How to calculate MAT when year as a dimension

Re: How to calculate MAT with two dimensions

hemanthaanichet — Mon, 13 Feb 2017 10:41:01 GMT

Ok Sunny.. iam will wait for your reply

have a great day....

Re: How to calculate MAT with two dimensions

sunny_talwar — Mon, 13 Feb 2017 12:40:04 GMT

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)

Re: How to calculate MAT with two dimensions

hemanthaanichet — Tue, 14 Feb 2017 09:59:05 GMT

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

Re: How to calculate MAT with two dimensions

sunny_talwar — Tue, 14 Feb 2017 10:08:32 GMT

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

Re: How to calculate MAT with two dimensions

hemanthaanichet — Tue, 14 Feb 2017 10:12:02 GMT

Sure sunny... Iam dng the same

Re: How to calculate MAT with two dimensions

hemanthaanichet — Tue, 14 Feb 2017 10:26:49 GMT

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

Re: How to calculate MAT with two dimensions

hemanthaanichet — Wed, 15 Feb 2017 06:18:42 GMT

Any clue r suggestion stalwar1‌

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

Re: How to calculate MAT with two dimensions

sunny_talwar — Tue, 28 Feb 2017 14:30:40 GMT

Try this

Re: How to calculate MAT with two dimensions

hemanthaanichet — Tue, 28 Feb 2017 15:44:46 GMT

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

Re: How to calculate MAT with two dimensions

sunny_talwar — Tue, 28 Feb 2017 15:49:39 GMT

Like this

Re: How to calculate MAT with two dimensions

hemanthaanichet — Tue, 28 Feb 2017 15:54:25 GMT

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

Re: How to calculate MAT with two dimensions

sunny_talwar — Tue, 28 Feb 2017 16:05:34 GMT

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?