Show customer favorite brand

[therealdees]

Hi!

I'm trying to show the customer's favorite brand based on it's shopping frequency, but I'm not sure how to do it.

The data model is working, I can filter a certain customer and see all of it's purchases, but I need to show in a single line the most shopped brand (respecting the calendar filter selection).

I think I came close with this expression, but it only shows something if I select Year and Month, otherwise it's null:

If(Aggr(Rank(Count(key_produto_sku)), produto_marca) = 1, produto_marca)

Customer ID field = key_cliente_fornec

product SKU = key_produto_sku

product brand = produto_marca

I thought maybe I could rank the counting of each SKU bought and aggregate it by it's respective brand, then if it's the first in the rank, show the brand name.

Not sure if I'm in the right path. Does anyone have a clue?

[Oleg_Troyansky · Accepted solution]

Hi there,

This is one of the trickier applications of AGGR(). I teach exactly the same solution as an exercise in my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik. The complete explanation is perhaps too long for a forum message, but the bottom line is this:

FirstSortedValue(key_cliente_fornec, -Aggr(Rank(Count(key_produto_sku)), key_cliente_fornec, produto_marca))

See if this formula gives you the desired results.

Join us at the Masters Summit for Qlik in Orlando or in Dublin to learn advanced Set Analysis, AGGR(), scripting, data modeling, performance, visualizations, and more - delivered by some of the best Qlik experts in the world!

Cheers,

[therealdees]

Hi, Oleg!!
 Thanks for the quick reply.

The expression you provided didn't work at first, but I tweaked it a bit and it worked with the following:

FirstSortedValue(produto_marca, Aggr(Rank(Count(key_produto_sku)), key_cliente_fornec, produto_marca))

I changed the FirstSortedValue first argument to product_brand (it was returning the customer ID) and removed the "-" (not sure why).

It actually worked for most customers, but some return null(). While looking at these null records I think it's because there's a "draw" between one or more brands (e.g Calvin Klein was bought 2x at the same time Adidas was bought 2x). If that's true, it's actually correct as I'm trying to show only a single record and not a list.

Thank you very much for your help!

If you don't mind explaining, how does the "-" sign affects the Aggr function?

[Oleg_Troyansky]

Good job tweaking the suggested solution!

The "-" was my mistake - in your case you don't need it. We use it when we evaluate expressions like Sales, where the highest number is the best, hence we are reversing the direction, to get the "first sorted value" in the descending order. In your case, the Rank() function is already reversing the direction - the highest number gets the lowest Rank 1, so the formula works correctly without the minus.

Cheers,

[therealdees]

Perfect!! Thanks again 🙂