How to count Rows for Top 3 vendors in Set Analysis Expression

[Anonymous]

I want to count no of rows (which I think is the same as counting ID - as its counting the number of instances where Level = '1', thats a req.)

for Top 3 vendors by Group. So the Expn should bring total count for top 3 vendors. For Eg.

Toal for XYZ will be (5+3+2) [5: Vendor A has Level 1 5 times, Vendor B has level 1 3 times, and Vendor C has level 1 3 times. These are the top 3 vendors for Group XYZ].

Similarly Total for ABC should be 3.

I want a straight table with dimension, Group only. The Expn sud calculate my above Requirements.

I am not looking for CALCULATED DIMENSION SOLN.  If you can  help that wpuld be great.

Thanks.

[Anonymous]

That exp is calculating how many vendors are in top 3 category for each group. For eg some times there can be more than 3 vendors in top 3 due to a tie:

Vendor A - total counts: 4

Vendor B - total counts: 4

Vendor C - total counts: 3

Vendor D - total counts: 2

so in this case, we have 4 "Top 3" vendors. this is what that current code is giving. I understand it is not easy with out a sample. but my qvw is huge. let me see if I can scramble it. I dont want to post the actual app for security reasons

[swuehl]

You can look into the additional arguments of Rank() function on how to create an appropriate tie break, maybe like

=Count({<Level = {1} >} Aggr( If( Rank(Count({<Level = {1}>} ID),4)<=3, Count({<Level = {1}>} ID)), Group,Vendor))

[Anonymous · Accepted solution]

swuehl, The correct answer is:

=Sum({<Level = {1} >} Aggr( If( Rank(Count({<Level = {1}>} ID))<=3, Count({<Level = {1}>} ID)), Group,Vendor))

The only thing I changed was use SUM instead of COUNT in the front. The code you wrote on which I based this was:

=Count({<Level = {1} >} Aggr( If( Rank(Count({<Level = {1}>} ID))<=3, Count({<Level = {1}>} ID)), Group,Vendor))

Thanks again!!

D

[Anonymous]

Swuehl,

Thanks for your help in all my post.

Cheers,