# New to QlikView

Discussion board where members can get started with QlikView.

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

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

Labels (2)

• ### max

1 Solution

Accepted Solutions
MVP

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

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 🙂

Contributor II

## Re: Expression to Count MAX value for individual dimension value

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

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

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)

Contributor III

## Re: Expression to Count MAX value for individual dimension value

Hey, can you try my solution?

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 🙂

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!

🙂

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