Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jerfwork
New Contributor II

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

Re: Expression to Count MAX value for individual dimension value

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))
15 Replies
gf
Contributor II

Re: Expression to Count MAX value for individual dimension value

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
New Contributor II

Re: Expression to Count MAX value for individual dimension value

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
Contributor II

Re: Expression to Count MAX value for individual dimension value

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

arpitkharkia
Contributor III

Re: Expression to Count MAX value for individual dimension value

Hi!

 

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

 

Regdars,

Arpit

jerfwork
New Contributor II

Re: Expression to Count MAX value for individual dimension value

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
Contributor III

Re: Expression to Count MAX value for individual dimension value

Hey, can you try my solution?

jerfwork
New Contributor II

Re: Expression to Count MAX value for individual dimension value

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
Contributor III

Re: Expression to Count MAX value for individual dimension value

Hey, no worries. You can try 

count({1}Customer2)

as your formula as it will ignore all filter selections.

I hope this helps!

🙂

jerfwork
New Contributor II

Re: Expression to Count MAX value for individual dimension value

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