topic Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW in QlikView

HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

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

Thanks

Neville

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 24 Jun 2020 15:14:51 GMT

Dear Members

Please help me on this

Thanks

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

Taoufiq_Zarra — Wed, 24 Jun 2020 16:08:13 GMT

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

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 24 Jun 2020 18:41:17 GMT

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

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 24 Jun 2020 18:44:36 GMT

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

Taoufiq_Zarra — Wed, 24 Jun 2020 18:47:57 GMT

can you share the file or the new sample

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 24 Jun 2020 18:50:15 GMT

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

Taoufiq_Zarra — Wed, 24 Jun 2020 19:05:36 GMT

If I understood correctly you want a Trimean by branch ?

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Wed, 24 Jun 2020 19:11:55 GMT

Yes Dear, that is what I need.

Thanks

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

Taoufiq_Zarra — Thu, 25 Jun 2020 09:56:23 GMT

@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

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

nevilledhamsiri — Thu, 25 Jun 2020 16:12:46 GMT

Dear Taoufiq, Thanks for your reply. I find it some what difficult to understand the way the script is written to go for the out come. But I will take my own time & try to learn from it more. For the moment my issue was resolved. Thank you again

Neville.

Re: HOW TO CALCULATE TRIMMEAN IN QLIK VIEW

Taoufiq_Zarra — Thu, 25 Jun 2020 16:53:20 GMT

Neville, Awesome! take your time and contact me if you have any questions.