topic Re: Expression to Count MAX value for individual dimension value in QlikView

Expression to Count MAX value for individual dimension value

jerfwork — Sat, 16 Nov 2024 03:50:11 GMT

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.

Re: Expression to Count MAX value for individual dimension value

gf — Wed, 17 Apr 2019 06:11:15 GMT

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

Re: Expression to Count MAX value for individual dimension value

jerfwork — Wed, 17 Apr 2019 06:18:21 GMT

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 🙂

Re: Expression to Count MAX value for individual dimension value

gf — Wed, 17 Apr 2019 09:00:28 GMT

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

Re: Expression to Count MAX value for individual dimension value

arpitkharkia — Wed, 17 Apr 2019 09:19:48 GMT

Hi!

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

Regdars,

Arpit

Re: Expression to Count MAX value for individual dimension value

jerfwork — Wed, 17 Apr 2019 09:35:05 GMT

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

Re: Expression to Count MAX value for individual dimension value

arpitkharkia — Wed, 17 Apr 2019 10:13:41 GMT

Hey, can you try my solution?

Re: Expression to Count MAX value for individual dimension value

jerfwork — Thu, 18 Apr 2019 00:53:05 GMT

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 🙂

Re: Expression to Count MAX value for individual dimension value

arpitkharkia — Thu, 18 Apr 2019 06:57:34 GMT

Hey, no worries. You can try

count({1}Customer2)

as your formula as it will ignore all filter selections.

I hope this helps!

🙂

Re: Expression to Count MAX value for individual dimension value

jerfwork — Thu, 18 Apr 2019 07:00:28 GMT

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

Re: Expression to Count MAX value for individual dimension value

arpitkharkia — Thu, 18 Apr 2019 07:03:10 GMT

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

Re: Expression to Count MAX value for individual dimension value

jerfwork — Thu, 18 Apr 2019 08:25:57 GMT

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

Re: Expression to Count MAX value for individual dimension value

sunny_talwar — Thu, 18 Apr 2019 11:12:21 GMT

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

Re: Expression to Count MAX value for individual dimension value

jerfwork — Sat, 20 Apr 2019 13:10:34 GMT

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!

Re: Expression to Count MAX value for individual dimension value

sunny_talwar — Tue, 23 Apr 2019 11:40:10 GMT

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

Re: Expression to Count MAX value for individual dimension value

jerfwork — Wed, 24 Apr 2019 01:17:21 GMT

Thank you Sunny! It's solved! 🙂