topic Re: How to Pull top 5 when there is a tie in QlikView

How to Pull top 5 when there is a tie

Anonymous — Sun, 21 Aug 2016 21:37:39 GMT

My question is to solve no 2) below. But first I want distincguish betn 1) & 2)

1) old post (qvw attached: "Rank_sort_1")

I was able to pull top 5 by using Count({ < FieldA = {'1'}, Rank ={'<= 5'}>} ID) [Rank is derived from your script]. This qvw has data at ID level granularity.

2) New post (qvw attached: "Rank_sort_2") This qvw already has count per vendor aggregated represented by the field [Regular count].

another diff is it does not have [FieldA] because the that req is already met. with avail data, how can I pull Top 5 vendor from ABC to ABCC but not ABEE in a pivot table keeping Group and vendor in dimensions.

Thank you again for your generous description and continuous support. much appreciated.

Re: How to Pull top 5 when there is a tie

Anonymous — Sun, 21 Aug 2016 21:44:30 GMT

This is a sequel to https://community.qlik.com/message/1108568#1108568

Re: How to Pull top 5 when there is a tie

sunny_talwar — Sun, 21 Aug 2016 22:07:22 GMT

Like this?

Re: How to Pull top 5 when there is a tie

Anonymous — Sun, 21 Aug 2016 22:24:15 GMT

yes this works in this sample. checking in my app.

Re: How to Pull top 5 when there is a tie

sunny_talwar — Sun, 21 Aug 2016 22:25:34 GMT

Sure thing

Re: How to Pull top 5 when there is a tie

Anonymous — Sun, 21 Aug 2016 22:46:04 GMT

it should have worked. its pulling only 1 Group with correct results. I must have messed something

Re: How to Pull top 5 when there is a tie

sunny_talwar — Sun, 21 Aug 2016 22:48:59 GMT

Can you try checking Always Fully Expanded on the properties tab?

Re: How to Pull top 5 when there is a tie

Anonymous — Mon, 22 Aug 2016 00:01:48 GMT

yes it was because of that. I have that option checked and its pulling top 5 the way I want it. I am trying to show total count for each Group; tried show partial sum for Group and/or vendor but no success

Re: How to Pull top 5 when there is a tie

sunny_talwar — Mon, 22 Aug 2016 00:04:03 GMT

Try this expression:

=Count(Aggr(Only({<Rank = {'<=5'}>}Count), Group, Vendor))

Re: How to Pull top 5 when there is a tie

Anonymous — Mon, 22 Aug 2016 00:14:02 GMT

it is counting the number of vendor occurrences in the table. I tried Sum(Aggr(Only({<Rank = {'<=5'}>}Count), Group, Vendor)) and it worked! Thank you so much sunny for sharing your genius and generosity. I am one lucky Qlikker

Re: How to Pull top 5 when there is a tie

sunny_talwar — Mon, 22 Aug 2016 00:42:05 GMT

Super