topic Re: top N by 2 dimensions table chart in App Development

top N by 2 dimensions table chart

rx98u5pq — Mon, 07 Apr 2025 08:27:11 GMT

Hi, this should be relatively straight forward and I have tried 3 approaches without it working:

I want the top 5 rows (not dimensions values) by #users in a straight table.
When i limit it by dimension I get the top 5 dimensions values which can mean more than 5 rows:
If i try a measure it does not work using aggregation:

#Users = count(distinct USER_ID)

AVG TIME = sum(TOTAL TIME] / count(distinct USER_ID)

am i missing something when i try the following?

=SUM(aggr(if(rank(count(distinct USER_ID))<= 5, count(distinct USER_ID)), [EVENT ID], ACTION))

I only want the green rows to show. As you can see event id is not unique and neither is Action

Re: top N by 2 dimensions table chart

sk88024 — Mon, 07 Apr 2025 09:52:28 GMT

Because in your data, Event ID = A and Action = 1 are present in two places,

So, in Qlik, the table will look like this. This is not something which can controlled using any sum, aggr or count function.

Also, to show top N only, you can use the limitation option for both dimensions.

Then, the table will look like this -

Re: top N by 2 dimensions table chart

rx98u5pq — Mon, 07 Apr 2025 13:34:19 GMT

Hi, thanks for the response but using the dimension filter for both dimensions does not work. it still bring back the top 5 dimension values, which is not the top 5 rows, e.g. there could be two rows for A here when you use the filter.
this measure ended up working:

If( Aggr( Rank( {<Event_ID = {"*"}>}[# Users], 1, 1 ), Event_ID ) <= 5, [# Users] )

Re: top N by 2 dimensions table chart

marcus_sommer — Tue, 08 Apr 2025 11:57:38 GMT

If you didn't want to aggregate the data on Event and Action you will need to add a further appropriate information to differentiate between the n records.