Finding mode of dimension then count of mode value within that dimension

[nickharshaw]

Hi all,

Hopefully, this isn't a repeat question. I've searched pretty extensively on the forums and couldn't find what I needed. 

Basically, I have a straight table of customer IDs with some high-level metrics associated with them, and one of the things I'm trying to calculate is "favorite brand" and "count of favorite brand pruchases".  I've found that I can find "favorite brand" with the following formulas:

FirstSortedValue(distinct brand, -purchase_id)

Mode(brand)

The only thing I can't come up with is a count of the mode (brand) value within a customer ID. Here's what I've tried:

count({<brand={"$=(mode(brand))"}>}customerkey)

count({<brand={"$=(FirstSortedValue(distinct brand, -purchase_id))"}>}dmkey)

Both of those values result in 0 records for each ID. Any suggestions? Thanks in advance for any help you can give!

[ChiragPradhan · Accepted solution]

Hi

Try this -

Max(Aggr(Count(Distinct purchase_id), customerkey, brand))

I have attached a sample, if it helps.

If it is what you are looking for, consider marking this as resolved.

Regards,

Chirag 

[nickharshaw]

That worked absolutely flawlessly for my application. Thank you so much for this, I was seriously stumped. 

[ChiragPradhan]

Glad I could help.