topic Re: Table Dimensions show top 10 only. in App Development

Table Dimensions show top 10 only.

WillJP — Tue, 29 Apr 2025 11:15:46 GMT

Hi,

I have a table with four dimensions, and I want to display only the Top 10 rows based on the largest values in one of those dimensions (let's call it Column X).

My goal is to:

Sort the table by Column X (descending)
Show only the top 10 values by Column X
Keep the table dimensions-only (no visible measures)

However, I’m running into a few issues:

The "Limitation" option is disabled when using only dimensions.
If I use Column X as a measure, it requires me to choose a dimension to group by, which doesn't give the correct granularity for my use case.
The only workaround I've found is to use Column X as both a dimension and a measure, then apply a limitation — but this displays Column X twice, and there’s no clean way to hide the extra column, which feels like a clumsy solution.

To complicate things further, I plan to apply this approach in another table where I’ll also need to filter one of the dimensions beforehand, so I want a reusable and clean method.

Is there a way to filter a table to the top N rows based on a dimension’s numeric value, without displaying the ranking field or duplicating columns?

Thanks!

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

Re: Table Dimensions show top 10 only.

Oleg_Troyansky — Tue, 29 Apr 2025 13:44:04 GMT

Hi there,

Yes, you can do it using a calculated dimension and the AGGR() function, in combination with advanced ("expression") search. Something like this:

Dimension X:

=AGGR( only({X={"=Rank(X)<=10"} } X), (X, NUMERIC, DESC))

There is quite a bit of syntax to unpack here. The AGGR() function, sorted by X in the descending numerical order, will provide the equivalent of a usual dimension. The ability to use Set Analysis with the advanced search allows you to limit X to the top 10 numerical values - presuming that the values of X are all numeric, as you mentioned.

Allow me to invite you to my session on Set Analysis and AGGR() at the Masters Summit for Qlik - coming up this fall to Hamburg, Germany.

Cheers,

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

Re: Table Dimensions show top 10 only.

WillJP — Tue, 29 Apr 2025 14:59:03 GMT

Hi Oleg,

I am currently struggling in getting this to function correctly.

I have substituted X with my actual field: [4.4.Product_Suspect.cumulative_total_net_amount_in_gbp].

Here is the formula I am using:

=AGGR(ONLY({\[4.4.Product_Suspect.cumulative_total_net_amount_in_gbp\]={"=Rank(\[4.4.Product_Suspect.cumulative_total_net_amount_in_gbp\])<=10"}}, \[4.4.Product_Suspect.cumulative_total_net_amount_in_gbp\]), (\[4.4.Product_Suspect.cumulative_total_net_amount_in_gbp\], NUMERIC, DESC))

Additionally, I need to display data from three other dimensions:

[4.4.Product_Suspect.billing_system]
[4.4.Product_Suspect.source_customer_number]
[4.4.Product_Suspect.material_number]

When I add all these as dimensions in my Expression, my calculation field returns "-"
And I get over 10 rows of data?

To summarise my ask:

If this were an Excel table, I would sort [4.4.Product_Suspect.cumulative_total_net_amount_in_gbp] from largest to smallest and then display the top 10 rows across all four dimensions.
Not grouping any dimension

Re: Table Dimensions show top 10 only.

Oleg_Troyansky — Tue, 29 Apr 2025 18:14:44 GMT

I could give you a more detailed and pointed advise if I could see a sample app with your data. It's hard to guess what's wrong there

Re: Table Dimensions show top 10 only.

WillJP — Wed, 30 Apr 2025 08:25:40 GMT

Hi,

I'm unable to give a sample app as I'm on my works environment.
I can advise I'm working on Qlik Sense August 2023 Patch 8

And here is some sample data.
My dimension do not contain unique fields and I do not want to group.

Re: Table Dimensions show top 10 only.

WillJP — Wed, 30 Apr 2025 13:55:33 GMT

Also just for reference.
In my original data set, I will need to do this for other fields.
As thought I may have to do some work in my load script if I cant do this in Expression