Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Larry,
look if result is correct. It may need some adjustments .... But gives result which looks good on first sight.
Let me know....
Thanks. Now I just need to figure out how to add the dynamic record count into the formula.
tell me more details about this dynamic record count if you need more help of course
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)
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