topic Re: Set Analysis using Rank in QlikView

Set Analysis using Rank

lsteere01 — Wed, 16 Apr 2014 14:26:14 GMT

I'm having difficulty with a set analysis. I want to create a chart that shows the average of a field, and on the same chart show what the average would be after removing the top 5 percent of the outliers.

In the load script I've defined a variable to get the record count minus 5%

vVisitsMinusOutliers = '=Round((Count(RAND) * .95))'; // returns the count of 95 percent of the total rows

in my expression I am using the following set analysis to filter out the top 5% of outliers but it's not working.

Avg({<$(vVisitsMinusOutliers) = { ">= $(=Rank(DELTA,4,1))"}>} LOS)

I've attached a sample qvw with some test data. Hopefully what I'm asking makes sense.

Thanks in advance.

Re: Set Analysis using Rank

Wed, 16 Apr 2014 16:33:11 GMT

Larry,

please check if there is some idea.

Let me know if you will find any tips.

I will try later to fight with it

Rank in Set analysis

Re: Set Analysis using Rank

Wed, 16 Apr 2014 16:42:33 GMT

Larry,

look if result is correct. It may need some adjustments .... But gives result which looks good on first sight.

Let me know....

Re: Set Analysis using Rank

lsteere01 — Wed, 16 Apr 2014 17:42:06 GMT

Thanks. Now I just need to figure out how to add the dynamic record count into the formula.

Re: Set Analysis using Rank

Wed, 16 Apr 2014 18:19:11 GMT

tell me more details about this dynamic record count if you need more help of course

Re: Set Analysis using Rank

lsteere01 — Wed, 16 Apr 2014 18:39:40 GMT

In your solution you hardcoded a 5 for the outlier value. It needs to be dynamic based on selections as in the example I originally provided.

In the load script I defined a variable to get the record count minus 5%

vVisitsMinusOutliers = '=Round((Count(RAND) * .95))'; // returns the count of 95 percent of the total rows

I tied modifying the expression you provided as follows but it didn't work

(Avg({<DELTA = { "=Rank(DELTA,4,1)<=$(vVisitsMinusOutliers)"}>} LOS), 0.01)

Re: Set Analysis using Rank

Wed, 16 Apr 2014 18:57:27 GMT

Larry,

do you know what result do you expect?

my expression should give you avg of LOS for set of records without those 4 ranked 1-4 (rank >=5)

rank is on DELTA value.

i think you dont need more dynamic, but maybe i do not understand your business needs.

So, please write more what records should be considered to this AVG or show me those records in chart or in xls

regards

Darek