Getting the modal average of a column containing strings

[#1]

Let's say I have two tables like:

Orders:

Order ID	User ID
ord1	1
ord2	2
ord3	1

Users:

User ID	User Name
1	Jordan
2	Clive

I have somewhat like the tables above being brought into Qlikview.

Is there a way, in a text object's expression, that I can print the user that has added an order the most amount of times (the Modal Average)? In the example above it would print Jordan, because he has added two orders where Clive has only added 1. This is of course a very simplified example, but it has the basic idea.

[Nicole-Smith · Accepted solution]

How about something like the attached?

The expression I used in the text box:

=concat({<[User ID]={'=count([Order ID])=$(=max(aggr(count([Order ID]),[User Name])))'}>}distinct [User Name], ', ')

I used concat() in case there are two (or more users) who have ordered the most amount of times.

[#3]

I'm struggling to figure out how this works - but it does! Thanks a lot.

[Nicole-Smith]

Okay, I can explain a bit:

Expression:

=concat({<[User ID]={'=count([Order ID])=$(=max(aggr(count([Order ID]),[User Name])))'}>}distinct [User Name], ', ')

The underlined part counts the number of [Order ID] for each user.

The bold part finds the max number of [Order ID] across all users.

So if the number of [Order ID] for a user is equal to the max number of [Order ID] across all users, we include that [User ID] and use that [User Name] in the concat().

Also, please mark helpful and correct answers so others can find solutions to their problems too