Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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.
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
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 |
Rob,
any suggestions?
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
Rob,
thanks but I am using personal edition so can't download this version. anyother way to view this file
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
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