topic Getting the modal average of a column containing strings in QlikView

Getting the modal average of a column containing strings

Thu, 20 Feb 2014 15:00:28 GMT

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.

Re: Getting the modal average of a column containing strings

Nicole-Smith — Thu, 20 Feb 2014 16:03:05 GMT

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.

Re: Getting the modal average of a column containing strings

Thu, 20 Feb 2014 16:10:33 GMT

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

Re: Re: Getting the modal average of a column containing strings

Nicole-Smith — Thu, 20 Feb 2014 16:14:52 GMT

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