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().
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
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.
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 proposed by @Chanty4u is incorrect.
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.
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.
