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]

Hi 

try this : 

[Table]:
load*,

if(isnull(DifferenceOfMedians) ,null(),
RangeFractile(0.5,DifferenceOfMedians,peek(DifferenceOfMedians,-1),peek(DifferenceOfMedians,-2))) as Median_2,
;

load *,

if(peek(product,-2)<>product ,null(),
RangeFractile(0.5,ClaimCount,peek(ClaimCount,-1),peek(ClaimCount,-2))) as Median_1_Rolling3Months,

ClaimCount-
if(peek(product,-2)<>product ,null(),
RangeFractile(0.5,ClaimCount,peek(ClaimCount,-1),peek(ClaimCount,-2))) as DifferenceOfMedians
;

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 ',');

you may get this table : 

may be at the end of your script group by client product and order by month , client , product also 

[sandp707]

Thank you Bruno ... it works good.

Regarding the Median_2 can we achieve it from the 4th line as in the screenshot.

[brunobertels · Accepted solution]

Hi,

Please check the below : 

if(isnull(DifferenceOfMedians) ,null(),
RangeFractile(0.5,DifferenceOfMedians,peek(DifferenceOfMedians,-1),peek(DifferenceOfMedians,-2),peek(DifferenceOfMedians,-3))) as Median_2,

[sandp707]

Thank you 

[sandp707]

Hello Bruno,

Thank you for the above solution. I want to create a for loop with 3 months of loop and bring the median. The concept is the same but needs to get used for-loop. Could you please help

Using the below string for the same:

Create a for loop to sort the months and get the middle/median value (URGENT)

[brunobertels]

Hi 

Sorry i'am not fluant enought in scripting for that 🙂 

May be another approach could be using a variable in back end so that the user can choose the number of month to take  in the rollong period 

regards

[sandp707]

Hi Bruno,

Even same with me 😃. But if you have that variable approach then I can give that solution also to the client 

[brunobertels]

Hello 

See App attached with dynamic rolling N month using Variable 

I create a slider with the variable object and create a variable called vNmonth in the variable panel menu 

It give a result looking like this : 

Exemple with 3 months selected : 

And below with 5 months selected :

Hope it Helps  

[sandp707]

Hi Bruno,

Could you please give the script and variable expression as I am working on Qlikview. As the qvf you gave is not supporting in QV.

Really thanks for the help.