HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

[nevilledhamsiri]

Dear All,

Please help me to write down an expression to calculate trimmean over the attached set of data.

Thanks

Neville

[nevilledhamsiri]

Dear Members

Please help me on this

Thanks

[Taoufiq_Zarra]

Hi @nevilledhamsiri 

I left the code long enough to understand the logic :

Script :

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 :

Formula of Trimean =avg({<IDtmp={">=$(Idmin) <=$(Idmax)"}>} [CLAIM PAID])

attached qvw file

[nevilledhamsiri · Accepted solution]

Dear Taoufiq,

Tanks a lot for your reply. When I add one more dimension which is Branch, what changes to be made for the mean calculation. Please see the working attached. The numbering wont come separately for two branches & the mean calculated is also different.

Please send me the revised script for this. Sorry for the troubles made,

Neville

[Taoufiq_Zarra]

can you share the file or the new sample

[Taoufiq_Zarra]

If I understood correctly you want a Trimean by branch ?

[nevilledhamsiri]

Yes Dear, that is what I need.

Thanks

[Taoufiq_Zarra · Accepted solution]

@nevilledhamsiri 

the new version of Script :

let percent=0.2;


File:
LOAD DATE, 
     BRANCH,
     [CLAIM PAID]
FROM
.\tream_mean.xlsx
(ooxml, embedded labels, table is Sheet1) where len(DATE)>0;

Tmp:
load distinct BRANCH as Br resident File;


let NumRows=NoOfRows('Tmp');

FOR i=0 to $(NumRows)-1

  LET vBRANCH=Peek('Br',$(i),'Tmp');
  
	Data:
	noconcatenate
	LOAD rowno() as IDtmp,
		 DATE as D, 
		 BRANCH as B,
	     [CLAIM PAID] as C
	    
	resident File where Match(BRANCH,'$(vBRANCH)')>0 order by [CLAIM PAID];
		
	Tmp1:
	
	load count(C) as CountClaim resident Data;
	
	let vcount=peek('CountClaim',0,'Tmp1');
	
	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);
	
	output:
	load '$(vBRANCH)' as BRANCH,Avg(C) as TreamBranch resident Data where IDtmp>='$(Idmin)' and IDtmp<='$(Idmax)';
		
	drop table Tmp2;
	drop table Data;
		  
NEXT;


drop table Tmp;

output :

attached Qvw file