Expression to Count MAX value for individual dimension value

[jerfwork]

Hi All,

I'm struggling with a expression in a report. The raw data below is saying what product has been sold by which Customer.

Customer	Category	Subcategory	ProductName
A	Bag	Totebag	TotebagA
A	Bag	Totebag	TotebagB
A	Shoes	Heels	HeelsA
B	Shoes	Heels	HeelsA
B	Shoes	Heels	HeelsA
B	Bag	Totebag	TotebagA
C	Shoes	Heels	HeelsA

I need to create a chart using 'Category' as dimension and show the MAX number of customer base on the highest number of purchase per category (Example, if 1 customer purchased 2 shoes and 1 bag, this customer should fall into Shoes category and should NOT show in Bag category. ). 

Example, I would like to get the expected result as below.

Category	Number of customer	Remarks
Bag	1	Customer A, because customer A purchased 2 Bags.
Shoes	2	Customer B and C, because customer B purchased 2 Shoes and customer C purchased 1 Shoes only

I been trying with below expression but the result is negative 😞

COUNT({<Category= {"$(=FirstSortedValue(DISTINCT Category,
-Aggr(Count(Customer), Category)))"}>} Customer)

MAX(Aggr(COUNT(ProductName),
Category,Customer))

Looking forward to your helps and thank you in advance! 🙂

[arpitkharkia]

Oh, ok. Can you share some samples explaining the issue? A qvw or an excel. That may help us more. 

[jerfwork]

Hello Arpit,

Currently i'm using "Category" as my dimension. For the expression, i'm using the solution that provided by Gf.

COUNT(AGGr(max(aggr(count(Category), Customer)),Customer))

The result is fantastic in the testing qvw file. However, when i put this expression into my real report (With more raw data info). The number seem hay-wired. I added "Customer" as my second dimension in order to verify the result, but I realized it is randomly grab Category instead of my expectation (Base on purchased the most of the category).  

Please see below screenshot for more understanding. Based on the screenshot below, the Category should show "Bags" instead of "Shoes". but i have no idea why it is showing "Shoes".

[sunny_talwar]

I have not seen what others have provided... but this seems to be working

Count(Aggr(If(Count(Category) = Max(TOTAL <Customer> Aggr(Count(Category), Customer, Category)), Category), Customer, Category))

[jerfwork]

Thank you Sunny! Your solution is working fine for me ! The information look correct.

May I ask if it is possible to add a condition into the expression? Because there is a situation like a Customer purchase 1 Bag and 1 Shoes. Using the solution that you provided, this customer will count as 2 instead of 1. Is it possible to get the latest purchase? Please see below reference.

Thank you very much!

[sunny_talwar · Accepted solution]

Here you are

Count(Aggr(If(
	(Max(TOTAL <Customer> Aggr(Count(Category), Customer, Category)) <> Min(TOTAL <Customer> Aggr(Count(Category), Customer, Category)) and Count(Category) = Max(TOTAL <Customer> Aggr(Count(Category), Customer, Category))) or
	(Max(TOTAL <Customer> Aggr(Count(Category), Customer, Category)) = Min(TOTAL <Customer> Aggr(Count(Category), Customer, Category)) and Category = FirstSortedValue(TOTAL <Customer> Category, -Date))
, Category), Customer, Category))

[jerfwork]

Thank you Sunny! It's solved! 🙂