Set Analysis using Rank

[lsteere01]

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.

[#2]

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

[#3]

Larry,

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

Let me know....

[lsteere01]

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

[#5]

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

[lsteere01]

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)

[#7]

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