Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lsteere01
Contributor
Contributor

Set Analysis using Rank

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.

6 Replies
Not applicable

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

Not applicable

Larry,

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

Let me know....

lsteere01
Contributor
Contributor
Author

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

Not applicable

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

lsteere01
Contributor
Contributor
Author

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)


Not applicable

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