For each dim1 (region), concat the top *n* dim2 va... - Qlik Community

nate_muir_anderson · ‎2023-03-08

Please note I am using qliksenseserver:14.5.22
Let me know
I have data like this

region	product	amount
South	Item	5
South	Toy	3
South	Stuff	2
North	Widget	30
North	Widget	20
North	Toy	10
North	Stuff	5
East	Stuff	1

Please notice that a given region might have more than one row for a given product

(for example there are two rows for region=North, product=Widget)

So I think I need to do AGGR, and I'm not sure I can use FIRSTSORTEDVALUE

How can I make a straight table like below...

(one row per region; shows the product and SUM(AMOUNT) for the product with the highest SUM(amount) (in that region))

region	top-selling-product
South	Item(5)
North	Widget(50)
East	Stuff(1)

Or a straight table like below...
(one row per region; shows the products and SUM(AMOUNT) for the top two products with the highest SUM(amount) (in that region), in descending order of SUM(amount))

region	top-2-selling-products
South	Item(5) Toy(3)
North	Widget(50) Toy(10)
East	Stuff(1)

This is the best I could come up with, it seems to work, but I feel like I over-engineered it (?)

Note it uses a rank() technique

CONCAT(
    AGGR(IF(rank(SUM(amount))<=2, product & SUM(amount)), 
        region, // otherwise "grain mismatch" as HIC says https://community.qlik.com/t5/Design/Pitfalls-of-the-Aggr-function/ba-p/1463275
        product), 
    ' ',
    // need the CONCAT sort_weight argument , right?
    // Arturo says that here: https://community.qlik.com/t5/Design/The-sortable-Aggr-function-is-finally-here/bc-p/1470305/highlight/true#M4511
    AGGR(IF(rank(SUM(amount))<=2, -SUM(amount)), 
        region, // otherwise "grain mismatch" as HIC says https://community.qlik.com/t5/Design/Pitfalls-of-the-Aggr-function/ba-p/1463275
        product)
)

This shows a Qlik table using the above expression, it produces the results I want:

This expression also works, not it uses a sortable aggr expressions technique, therefore it can use rowno() instead of rank().

CONCAT(
    AGGR(IF(rowno()<=2, product & SUM(amount)), 
        region, // otherwise "grain mismatch" as HIC says https://community.qlik.com/t5/Design/Pitfalls-of-the-Aggr-function/ba-p/1463275
        (product, (=SUM(amount),DESC))), 
    ' ',
    // need the CONCAT sort_weight argument , right?
    // Arturo says that here: https://community.qlik.com/t5/Design/The-sortable-Aggr-function-is-finally-here/bc-p/1470305/highlight/true#M4511
    AGGR(IF(rowno()<=2, -SUM(amount)), 
        region, // otherwise "grain mismatch" as HIC says https://community.qlik.com/t5/Design/Pitfalls-of-the-Aggr-function/ba-p/1463275
        (product, (=SUM(amount),DESC)))
)

Be careful of the sortable aggr expressions syntax, notice the expression is contained inside parentheses, and uses an equals sign as HIC says "Note the equals sign. This is necessary if you want to sort by expression." Specifically, the sort order must be inside

...(product, (=SUM(amount), DESCENDING))...

Not this:

...(product, (=SUM(amount)), DESCENDING)...

This would've worked too (sort by *negative* SUM(amount)), with implied ASCENDING order:

...(product, (=-SUM(amount))...

Here's the load statement I used

LOAD * INLINE [
region product amount
South Item 5
South Toy 3
South Stuff 2
North Widget 30
North Widget 20
North Toy 10
North Stuff 5
East Stuff 1
] (DELIMITER IS ' ');

Please let me know if this question is a duplicate question (already asked elsewhere)

I checked resources like these:

For each dim1 (region), concat the top n dim2 values (product), based on measure (SUM(amount))

Application Development

For each dim1 (region), concat the top *n* dim2 values (product), based on measure (SUM(amount))

Application Development

For each dim1 (region), concat the top n dim2 values (product), based on measure (SUM(amount))