Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jerfwork
Contributor III
Contributor III

Expression to Count MAX value for individual dimension value

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.

CustomerCategorySubcategoryProductName
ABagTotebagTotebagA
ABagTotebagTotebagB
AShoesHeelsHeelsA
BShoesHeelsHeelsA
BShoesHeelsHeelsA
BBagTotebagTotebagA
CShoesHeelsHeelsA

 

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.

CategoryNumber of customerRemarks
Bag1Customer A, because customer A purchased 2 Bags. 
Shoes2

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! 🙂

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

15 Replies
gf
Creator III
Creator III

First row second table, shouldn't there be
BAG - 2
Because customer A und B bought a bag?
if yes, have a look at the following...

jerfwork
Contributor III
Contributor III
Author

Hello Gf,

 

Thanks for your replied. 

The bag shouldn't be 2 because Customer B purchased Shoes more than Bags. Customer B should fall into "Shoes" category.

The expected result is to getting total number of unique customer. Meanwhile, the total number of "Number of customer" should not more than 3.

Thanks again 🙂

gf
Creator III
Creator III

Hello,
Sorry it's taken so long.
I updated my solution 🙂
Regards!

arpitkharkia
Creator III
Creator III

Hi!

 

The solution provided works fine. I also worked on the problem.  I used FSV, please find attachment.

 

Regdars,

Arpit

jerfwork
Contributor III
Contributor III
Author

Hi Gf,

 

The script is working and I'm getting unique number of customer. 

Unfortunately, while I verifying the info between the result and raw info, some customer fall into correct category but there are also some customer fall into wrong category.  (I added 1 more column(Customer) into dimension to do verification)

Please refer attached.. 

Capture.PNG

arpitkharkia
Creator III
Creator III

Hey, can you try my solution?

jerfwork
Contributor III
Contributor III
Author

Hi Arpit,

Sorry i have overlooked the message yesterday.

I have tried with the solution. However it only work if there is a fixed dataset.

If I do some filter/selection in the report, the number not be correct.

Thanks again for your help 🙂

 

arpitkharkia
Creator III
Creator III

Hey, no worries. You can try 

count({1}Customer2)

as your formula as it will ignore all filter selections.

I hope this helps!

🙂

jerfwork
Contributor III
Contributor III
Author

Hello Arpit,

 

Apologized if i didn't mention clearly in my initial message. Filter/Selection will be available in the report as the user need to view different year/month/week. .