Calculating median value using data from previous years

[John5]

I am having troubles with one of my expression. Need help with this.

I am trying to use the QlikView function RangeFractile() to calculate the median (50th percentile) of the sum of products over the last 3 years (same month from the previous three years). I want to display it in a line chart with the dimension YearMonth.

My expression: 
Aggr(RangeFractile(0.5,Above(
   Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -12), 'YYYYMM'))"}>} Products)
+ Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -24), 'YYYYMM'))"}>} Products)
+ Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -36), 'YYYYMM'))"}>} Products)
, 0, 1)), YearMonth)

The expression above does not generate the result I am expecting, not getting a median line in my chart.
Any ideas where I am going wrong ?

If I just use part of the expression:
Aggr(RangeFractile(0.5, Above(Sum(Products),0,36)), YearMonth)
With this expression I get a median line in my chart, but it is not calculated correctly since it's median value is based on all values in the past three years, not just the 3 same month values from previous years.

Any ideas are welcome.

Also see included example qvw and Excel file.

//J

[Chanty4u]

Try this 

Aggr(RangeFractile(0.5,Above(

   Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -12), 'YYYYMM'))"}>} Products)

+ Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -24), 'YYYYMM'))"}>} Products)

+ Sum({<YearMonth = {"$(=Date(AddMonths(Max(Date#(YearMonth, 'YYYYMM')), -36), 'YYYYMM'))"}>} Products)

, 0, 1)), YearMonth)

[Kushal_Chawda · Accepted solution]

@John5  create Year & MonthNum field in the script to sort your data in aggr function to pick above 3 values individually to put in rangefractile function.

then use below expression, You can remove alt () part from expression. I have used alt because for year 2018 there is nothing to compare for last 3 year so it produces null value. Instead showing Null I have use alt to provide actual value for that period

=sum(aggr(alt(RangeFractile(0.5, above( Sum(Products),1),above( Sum(Products),2),above( Sum(Products),3)),Sum(Products)),
(MonthNum,(NUMERIC,ASCENDING)),(Year,(NUMERIC,ASCENDING))))

  See the attached

[John5]

Thank you Kushal !!

Works perfectly, just what I was looking for.