topic Re: How to count Rows for Top 3 vendors in Set Analysis Expression in QlikView

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

Anonymous — Mon, 08 Feb 2016 22:36:04 GMT

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.

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

Anonymous — Mon, 08 Feb 2016 22:40:54 GMT

* Vendor C has Level '1', 2 times

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

swuehl — Mon, 08 Feb 2016 22:49:32 GMT

Try something like

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

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

Anonymous — Mon, 08 Feb 2016 23:31:48 GMT

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,

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

Tue, 09 Feb 2016 02:32:53 GMT

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

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

Anonymous — Tue, 09 Feb 2016 03:01:37 GMT

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

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

Tue, 09 Feb 2016 03:07:52 GMT

Try below:

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

Can you please share qvw with sample data.

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

swuehl — Tue, 09 Feb 2016 11:09:51 GMT

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))

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

Anonymous — Tue, 09 Feb 2016 15:43:58 GMT

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))

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

swuehl — Tue, 09 Feb 2016 15:46:13 GMT

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

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

Anonymous — Tue, 09 Feb 2016 16:08:22 GMT

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

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

swuehl — Tue, 09 Feb 2016 16:21:08 GMT

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))

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

Anonymous — Thu, 11 Feb 2016 04:47:03 GMT

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!!

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

Anonymous — Thu, 11 Feb 2016 04:49:40 GMT

Swuehl,

Thanks for your help in all my post.

Cheers,