TOP 10 meeting takers in month - ranking function

[#1]

Hi,

I read many discussions about usage of rank function and sorting different things and have no idea how to solve my problem .

I want to create a ranking of top 10 persons who take the highest number of meetings (monthly) using pivot table (or other table, chart etc. - something that will show data in correct order). So I want to have ranking (numbers from 1 to 10) as a first dimension in rows, months as a second dimension in columns and the names as a value. The names should be sorted by the highest volume of meetings for person taken in eachparticular month, so the order of names will differ in each column (month). Please help me with that, because I'm running out of the ideas how to do it.

We will have something like:

     Jan          Feb     Mar...

1 Person A Person B

2 Person B Person C

3 Person C Person A

....

10

[swuehl · Accepted solution]

Maybe like attached?

I created some sample data and a data island for a RankID like this in the script:

LOAD

'Person'&ceil(RAND()*10) as Person,

ceil(RAND()*12) as Month,

ceil(RAND()*10) as Meetings

AutoGenerate 2000;

LOAD

recno() as RankID

autogenerate 10;

Then created a pivot with dimensions RankID and Month, and

=aggr(if(rank(sum(Meetings),4)=RankID,Person),RankID,Month,Person)

as expression.

Hope this helps,

Stefan

[#3]

Thanks for a quick and personal help ! I'm very happy that my working Friday will end with this problem solved . This solution is perfect - easy and very useful!

[#4]

Now I'm working on a layout of this table. I'm trying to add a background color for every person on the rank list. The color depends on group to which the person is asigned, for ex. sales with blue color, marketing with red. Now it's not working when I add the expression with if(group='sales',blue(255)) etc. If you have an idea to solve this, I will be very grateful... Now I'm trying to do it myself, but with no results.

[swuehl]

Ok, I added 4 groups to my sample.

The problem is that your color expression is also evaluated in the context of your dimensions, RankID and Month. A group is ambiguous in this context, probably all groups are returned in the records for the Month (and RankID is a Data island). So you need to use an expression for your color selection, that returns the group for the appropriate RankID and Month.

So I just reused our above expression in a color pick (Group is just a numerical in my sample):

=pick( aggr(if(rank(sum(Meetings),4)=RankID,Group),RankID,Month,Person) ,blue(80),green(80),red(80),yellow(80) )

Hope this helps,

Stefan