Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted
Creator III
Creator III

Sorry Guys

I attached a worng QVW

Pls have a look on the corrected attachment below

Regards

Hemanth

Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted

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

Highlighted
Creator III
Creator III

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

Highlighted

You got it brother

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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

Highlighted

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