How to find Mid or Median of column values with Month and Product grouping in Qlik Sense

[sandp707]

Hello Everyone,

I am working on a requirement to find the Median of the Counts(below screenshots). Below is the full request 

Columns E, F, and G are the output columns that should be coming from the script. (Only from backend script)

Reference is the Product and their claim count
Column E - Rolling 3 months Median of the claim counts and so on

Column F - Difference between columns E and D

Column G - Rolling Median of Column F and it is rolling median till the current month (Not of 3 months)

sample data attached to excel

[brunobertels · Accepted solution]

Hello 

the script is just an inline load : 

[Table]:
LOAD * INLINE
[
Delivery_Month,Client_PTD,product,ClaimCount
202101,MannKind,Abugio,102
202102,MannKind,Abugio,105
202103,MannKind,Abugio,100
202104,MannKind,Abugio,104
202105,MannKind,Abugio,99
202106,MannKind,Abugio,68
202107,MannKind,Abugio,99
202108,MannKind,Abugio,102
202109,MannKind,Abugio,111
202110,MannKind,Abugio,125
202111,MannKind,Abugio,138
202112,MannKind,Abugio,140
202101,MannKind,Calmer,112
202102,MannKind,Calmer,156
202103,MannKind,Calmer,110
202104,MannKind,Calmer,105
202105,MannKind,Calmer,100
202106,MannKind,Calmer,104
202107,MannKind,Calmer,99
202108,MannKind,Calmer,68
202109,MannKind,Calmer,99
202110,MannKind,Calmer,102
202111,MannKind,Calmer,111
202112,MannKind,Calmer,122
](delimiter is ',');

I created a variable vNmonth and use a slider to select the N Month

then on a straight table, i added

Dimension :
Delivery_Month,
Client_PTD,
product,

Mesures :
sum(ClaimCount)

Median Rolling N month :

if(
isnull(aggr(above(product,$(vNmonth)-1),product,Client_PTD,Delivery_Month)),null(),

aggr(
RangeFractile(0.5,above(sum(ClaimCount),0,$(vNmonth)))
,product,Client_PTD,Delivery_Month)
)

difference of Median :

if(
isnull(aggr(above(product,$(vNmonth)-1),product,Client_PTD,Delivery_Month)),null(),

sum(ClaimCount) -
aggr(
RangeFractile(0.5,above(sum(ClaimCount),0,$(vNmonth)))
,product,Client_PTD,Delivery_Month)
)

Median 2 :
if(
isnull(aggr(above(product,$(vNmonth)-1),product,Client_PTD,Delivery_Month)),null(),

aggr(
RangeFractile(0.5,above(
sum(ClaimCount) -
aggr(

RangeFractile(0.5,above(sum(ClaimCount),0,$(vNmonth)))
,product,Client_PTD,Delivery_Month)
,0,$(vNmonth)))
,product,Client_PTD,Delivery_Month)
)

regards 

[sandp707]

Thank you Bruno for the help 👌🏻