Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Outliers

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?

12 Replies
Not applicable

Re: Outliers

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

Not applicable

Re: Outliers

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.

Not applicable

Re: Outliers

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

Not applicable

Re: Re: Outliers

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

ProductLowInformal HoursFormal HoursChannel Rework
Overdraft1.00.5159.6
Not applicable

Re: Re: Outliers

Rob,

any suggestions?

MVP & Luminary
MVP & Luminary

Re: Outliers

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

Not applicable

Re: Re: Outliers

Rob,

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

MVP & Luminary
MVP & Luminary

Re: Outliers

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

Not applicable

Re: Outliers

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

  +

  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