Table Dimensions show top 10 only.

[WillJP]

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

[Oleg_Troyansky]

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

[WillJP]

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

[Oleg_Troyansky]

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

[WillJP]

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.

[WillJP]

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