Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
sandp707
Contributor III
Contributor III

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

Hello Everyone,

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

sandp707_0-1655912091221.png

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

 

Labels (1)
11 Replies
brunobertels
Master
Master

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
Contributor III
Contributor III
Author

Thank you Bruno for the help 👌🏻