Counting of rows based on different attribut values

[WhatsUp]

Hey Guys,

I'm new to Qlik Sense and have a question.

I have a table with multiple rows, you can see an example in the image. Each row is an order from a customer. A customer can order different products. Each product is part of a product category. The product category has an ordinal ordering. In the example electronics is more valued then household and household is more valued then garden.

I'm interested to know how many customers ordered in which product category. The problem is I don't want to count a customer twice. Each customers should be counted in the highest product category out of which they bought a product. So in the end the customer amount should be equal to the number of orders in the categories.

Many thanks for your help.

[vinieme12]

As below

Electronics:

count({<Cat={'electronics'}>}distinct CusID)

Household:  (exclude customerID who have purchased electronics)

count({<Cat={'household'},CusID=e({<Cat={'electronics'}>}CusID)>}distinct CusID)

Garden:  (exclude customerID who have purchased electronics or household)

count({<Cat={'garden'},CusID=e({<Cat={'electronics','household'}>}CusID)>}distinct CusID)

[WhatsUp]

Thank you for your help, it works. But instead of a value for each, I would like to see the distinct values in a Pivottable as different rows. Right now if I use the category as rows in the pivottable I see the CustomerID muliple times instead of just ones.  Is there a way to achieve that?

Many Thanks!

[vinieme12]

this would need to be done in datamodel and not front end

can you post some sample data?

[WhatsUp]

The data has the form like in the image. There main part is:

customerID | Category 

with some more information like Cname/ OrderDate etc. The model has the form:

Customer:

SELECT distinct CustomerID

, category

,...

FROM Table 

WHERE x = y

Store * FROM Customer into [..qvd](qvd)