Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

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
Author

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
Author

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
Author

Rob,

any suggestions?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

Rob,

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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