topic Re: Displaying the top value in a text box in QlikView

Displaying the top value in a text box

Sun, 03 Apr 2016 04:11:04 GMT

I have a table like the following

UserNamejavascript:;	Team	Count
User1	Italy	1
User2	Spain	1
User3	Germany	1
User4	Italy	1
User5	Germany	1
User6	Italy	1

I would like to have a Text Box that displays "Italy" as being the most popular choice...regardless of any selections that are made on the data.

I have seen some solutions for this but if I had a list box based on username and I selected e.g. User5, the value displayed in the text box would change to "Germany" and would not display "Italy" which is, regardless of the selection, still the most popular choice.

This seems such a simple thing to do but I cannot see a simple solution for it.

Re: Displaying the top value in a text box

oknotsen — Sun, 03 Apr 2016 08:49:04 GMT

If you have the expression that shows the right country without a selection but the wrong country with a selection, could you share it with us? I think I know the solution to your problem, being "use Set Analysis", but I need your expression to show you how to add it.

Re: Displaying the top value in a text box

kkkumar82 — Sun, 03 Apr 2016 09:18:46 GMT

=Concat(Distinct {<Team={'Italy'},Count,UserName>}Team,',')

Hope this may help you.

Please find attached qvw.

Thanks

Kiran Kumar

Re: Displaying the top value in a text box

sunny_talwar — Sun, 03 Apr 2016 10:21:41 GMT

Try this:

Expression:

=Concat(DISTINCT {<Team = {"=Rank(Count({1}Team)) = 1"}, UserName>}Team, ', ')

=Only({<Team = {"=Rank(Count({1}Team)) = 1"}, UserName>}Team)

Re: Displaying the top value in a text box

Sun, 03 Apr 2016 10:36:56 GMT

I was using the expression from your article

=only(if(aggr(Rank(Sum(WinCount)),Winner)=1,Winner))

Re: Displaying the top value in a text box

Kushal_Chawda — Sun, 03 Apr 2016 10:54:14 GMT

see this

I have created th Field COUNT considering that if it is not available in table

Table:
LOAD [UserNamejavascript:;] as UserName,
Team,
1 as COUNT,
Count
FROM
[https://community.qlik.com/thread/211911]
(html, codepage is 1252, embedded labels, table is @1);

Expression

=FirstSortedValue({1}Team,- aggr(Count(Team), COUNT))

Re: Displaying the top value in a text box

Sun, 03 Apr 2016 10:58:05 GMT

What if Spain was actually the most popular, I'd want it to display "Spain" with having to change the formula

Re: Displaying the top value in a text box

Sun, 03 Apr 2016 11:14:31 GMT

If I use this

=Concat(DISTINCT {<Team = {"=Rank(Count({1}Team)) = 1"}, UserName>}Team, ', ')

The text box with the formula is blank when I make a selection from the list box

If I use this

=Only({<Team = {"=Rank(Count({1}Team)) = 1"}, UserName>}Team)

I only get "Italy" displayed if I pick a user that has "Italy" as the choice. If I pick a user who has "Spain" the text box displays "-"

Re: Displaying the top value in a text box

Sun, 03 Apr 2016 11:20:01 GMT

I'm not sure how this works yet, but it does.

It also works for me without adding the COUNT field.

Re: Displaying the top value in a text box

sunny_talwar — Sun, 03 Apr 2016 18:12:42 GMT

Have you checked out the screenshot and the attached sample? In the screenshot, I have made a selection on UserName field (User 5) and we are still seeing Italy.

Re: Displaying the top value in a text box

Sun, 03 Apr 2016 20:59:03 GMT

I did check out the screen shot but I only have the desktop version so can't open the qvw file.

Re: Displaying the top value in a text box

Kushal_Chawda — Mon, 04 Apr 2016 04:29:35 GMT

Awsome

Re: Displaying the top value in a text box

sunny_talwar — Mon, 04 Apr 2016 09:46:16 GMT

Since you have your solution, there is no point going over my proposed way of doing it

Re: Displaying the top value in a text box

Thu, 14 Apr 2016 11:31:15 GMT

As it turned out there was a problem with the original solution and I went back to your suggestion and it did actually work as you described.

Re: Displaying the top value in a text box

Thu, 14 Apr 2016 11:34:16 GMT

This solution did work initially but for some reason as the data changed the most popular team always remained the same regardless of how the data changed.

Re: Displaying the top value in a text box

sunny_talwar — Thu, 14 Apr 2016 11:42:33 GMT

Great, I am glad it did help you out in the end