Top X number of Rows by Top 10 Total

[#1]

Hello all.

I am trying to create an object that contains Doctors, their specialties, the drugs they have prescribed and the sum of the cost for those drugs.

However, the object should contain only those doctors whose combined total cost for the month is in the top 10 for the entire business.

I am trying to accomplish this in a straight table. I have been able to calculate the doctors overall rank by total cost.

My problem: the number of rows returned for the top 10 doctors is not static, so I cannot set the max rows equal to a number. I have tried putting an expression into "Max Numbers" but it does not seem to work.

Here is the expression:

=max(If(aggr(rank(sum(RxPrice)/sum(total RxPrice)),Dr_Name)<=10,rownum(total)))

Any help/suggestions would be greatly appreciated.

Thanks

[Oleg_Troyansky]

One way is to use set analysis for all your expressions:

sum( {<Dr_Name={"= rank(sum(RxPrice))<=10"} >} ....)

You may need to add another Set Analysis condition inside the inner "sum", to limit revenues just to the current Month...

[#3]

Thanks for the response.

I initially had it working for just the doctor, using this:

=If(aggr(rank(sum(RxPrice)/sum(total RxPrice)),Dr_Name)<=10,Dr_Name)

The result was 11 rows of data, 10 specific to doctors and the 11th was the total for all non-top 10 doctors.

When I added in the specialty and drug, the 11th row expanded to n rows because the table was grouping by blank name, specialty, and drug name.

In attempt to get consolidate all the non-top 10 rows again, I tried coding my other two dimensions like this:

=If(aggr(rank(sum(RxPrice)/sum(total RxPrice)),Dr_Name)<=10,Specialty)

=If(aggr(rank(sum(RxPrice)/sum(total RxPrice)),Dr_Name)<=10,Drug)

These did not help.

[#4 · Accepted solution]

I was able to limit by simply supressing null values on the Dr_Name field. I am not certain if this is the most elegant approach but it did provide my desire outcome.