topic Re: Outliers in QlikView

Outliers

Thu, 29 May 2014 13:15:13 GMT

Hi,

I am happy with my expression calculations but want to get rid of outliers from that. how can I do please. I tried a few outlier threads but could not solve it. Any suggestions?

Re: Outliers

Thu, 29 May 2014 13:17:05 GMT

Are you trying to completely remove outliers? Or just trying not to show them in a chart? It would be easier to answer if you could share a sample qvw

Re: Outliers

Thu, 29 May 2014 13:28:23 GMT

I have a table showing time in working hours. I get the average of 159.6 which is correct but actually there are only two entries one is with 2.3 and the other 316.9 and the rest (126) are zero so average 159.6 (319.20/2, zeros are ignored in calculations). Is it posstible to ignore some % of lower and higher entries.

Re: Outliers

Thu, 29 May 2014 13:32:12 GMT

Have you tried using set analysis to only perform the calculation on values within a given range? Without seeing what you're working with its hard to give you an expression though

Re: Re: Outliers

Thu, 29 May 2014 13:38:00 GMT

I am using the following expression and happy with this calculation ( do you have any better way to calculate time in working dyas and working hours). I am new to qlikview . could you please tell step by step.

avg(fabs(interval(((Networkdays(Date(TerminationFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date(ReferralLastOnly,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

if(frac(date(TerminationFirstTimestamp))<num('$(vQuitTime)'),if(frac(date(TerminationFirstTimestamp))>num('$(vStartTime)'),Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(TerminationFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

if(frac(date(ReferralLastOnly))>num('$(vStartTime)'),if(frac(date(ReferralLastOnly))<num('$(vQuitTime)'),(Date(ReferralLastOnly,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss')))*24

ProductLow	Informal Hours	Formal Hours	Channel Rework
Overdraft	1.0	0.5	159.6

Re: Re: Outliers

Thu, 29 May 2014 14:26:30 GMT

Rob,

any suggestions?

Re: Outliers

rwunderlich — Thu, 29 May 2014 14:58:58 GMT

See this example:

Qlikview Cookbook: Outliers http://qlikviewcookbook.com/recipes/download-info/outliers/

The example will show you how to remove outliers using fractile(),

-Rob

Re: Re: Outliers

Thu, 29 May 2014 15:03:12 GMT

Rob,

thanks but I am using personal edition so can't download this version. anyother way to view this file

Re: Outliers

rwunderlich — Thu, 29 May 2014 19:58:34 GMT

Hi Shoaib,

Download the older Qlikview Cookbook zip from from Downloads - Rob Wunderlich Qlikview Consulting

That version of the Cookbook is personal edition enabled.

Unzip it and open the Cookbook.qvw, and find the Outliers example.

-Rob

Re: Outliers

Fri, 30 May 2014 14:20:33 GMT

Hi Rob,

thank you, I downloaded outliers example but still struggling. I have different type of data. I used the following expression (called 'Rework') to get the difference between two timestamps in working hours. Should I amend this expression and if yes then how to remove outliers. If should keep the same expression and use this calculated fig called Rework in the outliers formula or what?

avg(fabs(interval(((Networkdays(Date(TerminationFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date(ReferralLastOnly,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

Re: Outliers

Tue, 03 Jun 2014 13:41:32 GMT

Hi Rob,

Any help on my earlier post please

Re: Outliers

rwunderlich — Tue, 03 Jun 2014 17:21:15 GMT

Sorry for the lack of response. I don't have a comment on your expression, because I'm not clear what it's doing just by looking at it. Can you post a sample QVW?

-Rob

Re: Re: Outliers

Wed, 04 Jun 2014 09:13:19 GMT

Hi Rob,

No problem. sorry I can't share the file because of the sensitivity of the data. But this expression calculates Monday to Friday 9am to 5pm difference in hours between two time stamps. Is there any other expression who calculate two timestamps difference in hours based on monday to friday and 9am to 5pm and also exclude outliers