Trimean In Qlikview

Taoufiq_Zarra — Wed, 21 Sep 2022 17:07:35 GMT

Trimean : Mean, excluding outlier

Hi,

Recently I helped a community member to calculate the Trimean, I found this indicator interesting, so I decided to share it here as well.

The trimean (TM), or Tukey's trimean, is a measure of a probability distribution's location defined as a weighted average of the distribution's median and its two quartiles: (1)

trimean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis (2)

Example :

for the following example :TRIMMEAN is 3.778

LOAD * INLINE [ Data 4 5 6 7 2 3 4 5 1 2 3 ];

To calculate Trimean on Qlikview, we will take the example below :

DATE	CLAIM PAID
02/01/2020	250
05/01/2020	750
08/01/2020	25000
11/01/2020	50000
14/01/2020	75000
17/01/2020	2500
20/01/2020	3500
23/01/2020	500000
26/01/2020	850000
29/01/2020	25000
01/02/2020	10000
04/02/2020	1800000
07/02/2020	50000
10/02/2020	65000
13/02/2020	45000
16/02/2020	1500000
19/02/2020	450000
22/02/2020	18500
25/02/2020	25000
28/02/2020	50000
02/03/2020	75000

the expected output is :

Normal Average	267 643
Trimmean	201 066

the script can be presented as :

Data0: LOAD DATE, [CLAIM PAID] FROM [.\TRIMMEAN(1).xlsx] (ooxml, embedded labels, table is Sheet1); Data: noconcatenate LOAD rowno() as IDtmp, DATE, [CLAIM PAID] resident Data0 order by [CLAIM PAID]; drop table Data0; let percent=0.1; Tmp1: load count([CLAIM PAID]) as CountClaim resident Data; let vcount=peek('CountClaim',0,'tmp'); drop table Tmp1; let k=floor($(vcount)*($(percent))/2); Tmp2: load min(IDtmp) as minIDtmp, max(IDtmp) as maxIDtmp resident Data; let Idmin=peek('minIDtmp',0,'Tmp2')+$(k); let Idmax=peek('maxIDtmp',0,'Tmp2')-$(k); drop table Tmp2;

output :

article Trimean In Qlikview in Member Articles

Trimean In Qlikview