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]

* Vendor C has Level '1', 2 times

[swuehl]

Try something like

= Count({<Level = {1}, Vendor = {"=Rank(Count({<Level = {1}>}ID)<=3"}>} ID)

[Anonymous]

Hi Swuehl,

Thank you again!!. Your code was perfect except that it was missing one parenthesis after ID,

so when I added one more, it worked: here is the successful code :

= Count({<Level = {1}, Vendor = {"=Rank(Count({<Level = {1}>}ID))<=3"}>} ID)

because you have used two functions Rank and Count, you probably forgot to close one more parenthesis, which is no big deal at all. I appreciate your help.

Many Thanks,

D

[#5]

QlikDash, please answer your question as answered if you get the answer. It will useful to others.

[Anonymous]

Dathu,

The code is not working 100%, I will mark it once It is all set.

Swuehl,

There is a small Problem with the code. In the actual app, I have about 30 Expns and 1 dimension. The Expn we are working on is 1 out of 30 in the straight table. The Expn does not show the correct value, unless I select a field value from List Box 'Group' which is the dimension on the straight table. For Example unless I select value 'XYZ' from the Group list box, its giving me a value of less than 5. But Once I select a value from the list box, it displays correct result for corresponding Group. I  do not know why.

Thanks

[#7]

Try below:

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

Can you please share qvw with sample data.

[swuehl]

Either create a composite key made out of Group and Vendor in your data model,

LOAD

     Group,

     Vendor,

     Autonumber(Group & Vendor) as GroupVendor,

     ...

Then use

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

Or without changing your data model, use an expression like

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

[Anonymous]

Hi Swuehl,

I am getting an error saying "Error in Set Modifier Expression" for

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

[swuehl]

Yeah, sure, I missed the >} after the first set expression.

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

It's quite hard to code expressions only in the editor, without having a sample to work with