topic aggr and rank in App Development

aggr and rank

stefano_marson — Mon, 13 Oct 2025 17:01:12 GMT

Hi Everyone,

In Qlik Sense, I have a pivot table with two dimensions: Store and Item.
I would like to write an expression that, at the Store dimension level, counts the number of Items that are not sold in the Store but are among the 100 best-selling items across all stores.
Instead, in the rows where the Item dimension is exploded, I would like it to show the Item with a measure value of 1 if the Item has not been sold but is among the top 100 across all stores.
I wrote the following expression, but it is incorrect.

Sum(
Aggr(
If(
// Item NOT sold in Store
sum(Sales)=0
AND
// Item in Top 100 throughout the entire store network
rank(sum(TOTAL <Item> {< Store= >} Sales), 4) <= 100,
1, 0
),
Store, Item
)
)

I believe the problem lies in the fact that rank() is not calculated at the Item level and Items are not sorted based on rank().

Thanks in advance

stefano

Re: aggr and rank

Chanty4u — Mon, 13 Oct 2025 18:33:52 GMT

Try this

Sum(

Aggr(

If(

Sum(Sales) = 0

AND Rank( TOTAL Sum( {1} Sales ) ) <= 100,

Store, Item

)

Re: aggr and rank

Amit_Prajapati — Tue, 14 Oct 2025 08:53:34 GMT

Hi @stefano_marson ,

As @Chanty4u shared this expression counts how many Store-Item combinations have zero sales and are ranked in the top 100 based on total sales across all data, ignoring current selections and aggregate on Store and item

Sum(
Aggr(
If(
Sum(Sales) = 0
AND Rank(TOTAL Sum({1} Sales)) <= 100,
1,
0
),
Store, Item
)
)

Re: aggr and rank

stefano_marson — Tue, 14 Oct 2025 09:11:46 GMT

Hi @Chanty4u,

I would like to clarify that total network sales must be calculated for each item.
I must consider the item if it has sales = 0 compared to the store and be among the top 100 items considering sales across the entire network, i.e., once the item has been fixed and considering all stores.

So unfortunately, the expression you suggest is incorrect.

Thank you

stefano

Re: aggr and rank

stefano_marson — Tue, 14 Oct 2025 09:14:10 GMT

Hi @Amit_Prajapati,

So unfortunately, the expression proposed by @Chanty4u is incorrect.

Thank you

stefano

Re: aggr and rank

Amit_Prajapati — Tue, 14 Oct 2025 09:55:42 GMT

Hi @stefano_marson ,If possible, please share sample data along with the expected output. That would help in reviewing and validating the issue more effectively.

Re: aggr and rank

marcus_sommer — Tue, 14 Oct 2025 14:55:58 GMT

I could imagine applying something like this:

-(sum(Sales)=0) * aggr(nodistinct -(rank(sum(TOTAL Sales), 4)<=100), Item)

to simplify the approach and making the parts separately testable.

Further important will be that the first condition is mostly only workable if there are ZERO/NULL records for each Item which is usually rather seldom by fact-data. More common is that these records are missing. In this case you may try to extend it to something like: rangesum(sum(Sales), sum({1} 0)) but within the majority of scenarios it won't work and you will need to populate the missing data.