Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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
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)
@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
Thank you Kushal !!
Works perfectly, just what I was looking for.