Ranking and Overall Averages...use prefilter?

[dinos]

Hi,

I have a simple report.  I want to rank the top 5 vendors by order qty, as well as show the overall average for all vendors.

To get my top 5, I have a detailed filter on Rank < 6 set to before aggregation.  The problem is this filter gets processed before the overall average gets computed, so my overall average is based on those top 5 vendors, not the entire vendor population.

So I tried the 'after aggregation' option, and I get all rankings, and my overall average is based on all vendors.

What am I doing wrong?  Do I need to do something on the Rank function, like prefilter?  How does the prefilter option work anyways?

[MFGF]

Hi,

The way I see it, you have two approaches you could take to achieve this.

One is to calculate the overall average in a different query, then UNION this to your query containing your top 5 vendors, and base your report on the results of the UNION.

The other is to add a calculation to your report with an expression like: if ([Rank] <=5) then ('Best 5') else ('Others')
Include this calculation in the Properties property of your list, then conditionally hide the rows where the calculation is equal to 'Others'

Good luck!

MF.