Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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! 🙂
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))
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...
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 🙂
Hello,
Sorry it's taken so long.
I updated my solution 🙂
Regards!
Hi!
The solution provided works fine. I also worked on the problem. I used FSV, please find attachment.
Regdars,
Arpit
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..
Hey, can you try my solution?
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 🙂
Hey, no worries. You can try
count({1}Customer2)
as your formula as it will ignore all filter selections.
I hope this helps!
🙂
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. .