topic Re: set analysis/filter problem in QlikView

set analysis/filter problem

Thu, 05 Feb 2015 13:03:13 GMT

I am trying to calculate an average lead time per stock no and exclude the outliers (values under the 25th percentile and values over the 75th percentile).

Thanks to this community I was able to construct/refine my formula which is working. However there seems to be some behaviour which I do not understand.

The "Avg. lead time (excl. outliers)" contains the following expression:

"=avg(

total <[Stock no]>

{<[Lead time (days)]={"">=$(=fractile(total<[Stock no]> [Lead time (days)],$(Percentile_Min))) <=$(=fractile(total<[Stock no]> [Lead time (days)],$(Percentile_Max)) )""}>}

[Lead time (days)])"

When the model is filtered to a specific stock no the table below shows the expected (CORRECT) results. On this occasion 98 is the 25th percentile and 128 is the

75th percentile and the expression is averaging 98, 100 and 128 as they fall within the criteria. The values 132 and 70 are excluded which is what I want.

However, when the model is unfiltered and although the percentile values remain unchanged, the expression seems to interpret the lead time values differently

and as a result, it is excluding the 128 lead time. It is only averaging the 98 and 100 returning a result of 99…?

Can anyone tell me why and better still suggest a solution?

Many thanks

Oli

Re: set analysis/filter problem

sunny_talwar — Thu, 05 Feb 2015 13:12:19 GMT

I am not an expert, so experts, correct me if I am wrong, but set analysis take a single value for the chart and hence when you have not selected on stock no, it may be calculating a Fractile number based on all the stock no. I would have asked you to use aggr, but it doesn't work within set analysis because of the same reason that set analysis takes a single value for a chart. The only solution I can think of is to calculate 25th and 75th Fractile in the script using group by statement and then use that in your chart.

Hope that helps.

Best,

Re: set analysis/filter problem

Thu, 05 Feb 2015 15:31:43 GMT

Thanks for the reply

Sadly I can not get this to work in the script. So I am not sure if there is a solution for my problem??

Oli

Re: set analysis/filter problem

sunny_talwar — Thu, 05 Feb 2015 16:03:26 GMT

You don't have access to the script or you are not sure how to do this within the script? If you can share some dummy data, I may be able to help you.

Best,

Re: set analysis/filter problem

Fri, 06 Feb 2015 15:17:54 GMT

I have access to the script but I am not sure I can load an example easily due to the models size and complexity. I will see what I can come up with.

Thanks again.

Re: set analysis/filter problem

sunny_talwar — Fri, 06 Feb 2015 15:27:17 GMT

Not a problem. Hope you are able to achieve what you are trying to do.

Best,