topic Outlier Detection in QlikView

Outlier Detection

Anonymous — Fri, 13 Jan 2017 14:06:08 GMT

Hi,

Need your help in outlier detection by chart through standard deviation,

I have multiple GL accounts like payroll, cash, banking, fixed asset that have monthly amount posted. I am trying to see if there was any higher than normal value posted to a particular account in a month.

E.g. if every month payroll has 100, 110, 90, 110, 100 values and all of a sudden 600 pops up, then that should be the outlier.

I have attached the image also with an outlier showing at the top.

Hope I am able to explain what's needed. Appreciate the help

Thanks,

Re: Outlier Detection

marcus_sommer — Fri, 13 Jan 2017 14:16:42 GMT

I think this will be helpful: Recipe for a Box Plot.

- Marcus

Re: Outlier Detection

Anonymous — Fri, 13 Jan 2017 14:22:32 GMT

Marcus I would prefer something like what I attached, that's easier to interpret by the users

Re: Outlier Detection

marcus_sommer — Fri, 13 Jan 2017 14:31:19 GMT

You don't need to use the box plot chart - you could add the logic to a line- or barchart, too.

- Marcus

Re: Outlier Detection

MarcoWedel — Sat, 14 Jan 2017 01:25:54 GMT

Hi,

maybe one example could be:

hope this helps

regards

Marco

Re: Outlier Detection

Anonymous — Mon, 16 Jan 2017 10:42:33 GMT

Thanks Marcus, I will try that

Re: Outlier Detection

Anonymous — Mon, 16 Jan 2017 10:42:53 GMT

Thanks a lot Marco, I will try this and let you know

Re: Outlier Detection

Anonymous — Mon, 16 Jan 2017 11:50:54 GMT

Marco, this is great, can you suggest if we can use standard deviation in the expression? The outlier will be anything greater than 2 or 3 times the standard dev

Re: Outlier Detection

MarcoWedel — Mon, 16 Jan 2017 23:48:31 GMT

maybe something like this?

hope this helps

regards

Marco

Re: Outlier Detection

Anonymous — Tue, 17 Jan 2017 10:33:00 GMT

Thanks a lot Marco, this is really awesome

Quick question on the pivot though , how did you get the colors in for the outliers and how did you transpose it to columns? mine looks like this

And about the std dev chart, how did you get the 2, 3 sigma line and also when I click on a particular outlier, say for account 2, all the account numbers show up, but in your chart, just that value gets selected which is what I want, what am I missing?

Re: Outlier Detection

MarcoWedel — Thu, 19 Jan 2017 00:57:41 GMT

Hi,

regarding your questions:

The pivot chart highlights are defined by a background colour expression:

To pivot a dimension to the top you just drag and drop the dimension column to the upper right side of the chart:

The 2and 3σ limit lines are using the "Reference Lines" chart option:

The chart dimension value selection is left to standard, no special settings applied here.

Please close your thread if your initial question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: Outlier Detection

Anonymous — Thu, 19 Jan 2017 11:07:23 GMT

Thanks Marco, it helps. I have marked that correct