Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
hemanthaanichet
Creator III
Creator III

How to calculate MAT when year as a dimension

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

Capture.PNG

View solution in original post

24 Replies
hemanthaanichet
Creator III
Creator III
Author

Sorry Guys

I attached a worng QVW

Pls have a look on the corrected attachment below

Regards

Hemanth

sunny_talwar

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

Capture.PNG

hemanthaanichet
Creator III
Creator III
Author

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

sunny_talwar

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

hemanthaanichet
Creator III
Creator III
Author

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

sunny_talwar

You got it brother

hemanthaanichet
Creator III
Creator III
Author

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

hemanthaanichet
Creator III
Creator III
Author

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

sunny_talwar

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