Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ElsKnockaert
Contributor II
Contributor II

aggr in set analysis compare with set value

Hello everybody

I have an issue with 

ElsKnockaert_0-1620374665230.png

#BC :    =count (distinct Cust_Id)

% Repeat Purchase:    = (count({$}distinct Order_Id) - column(1) )/ column(1)

#Orders : =count({$}distinct Order_Id) 

 

What I want:

In the column % Repeat Purchase I want to see the % of customers who have had more than 1 order linked to them. 

Current:

Doesn't take into consideration that a customer could also have 3 orders. In which case the % should stay at 2.27%, but now it would be 4.55% .

I've tried (and many variantions) but it's not working:

count({$< aggr(count(Order_Id), Cust_Id)={">1"} >} , Cust_Id)

 

Labels (3)
1 Solution

Accepted Solutions
OmarBenSalem
Partner
Partner

I'll assume this is it :

your measure would be something like

count(distinct {<CustID={"=count(distinct Orders)>1"}>} CustID)/count(distinct CustID)

which results in :

Capture.PNG

View solution in original post

6 Replies
OmarBenSalem
Partner
Partner

Can you present your date in a simple way? maybe an inline table with 6 lines? and explain with them the result needed? It would be much easier to help when we have data to work with !

Thanks

ElsKnockaert
Contributor II
Contributor II
Author

Of course! I hope this helps:
ElsKnockaert_0-1620377153027.png

I would like a 3rd column next to #Orders that shows a % of how many distinct customers have had more than 1 order. 


In this example, User1 has 44 distinct customers who have placed an order. But 45 Orders have been made in total, so 1 customer has placed 2 orders. so in the 3rd column I would like to see [amount of distinct customers with multiple orders]  / [amount of distinct customers with at least 1 order = column(1)]. This would be 2.27% in the case of User1.
If I just do (#orders-column(1) ) / column(1) , it doesn't take into account that 1 customer could have placed 3, 4, 5, or more orders. 
If User1 has 44 customers with at least 1 order, but placed 46 orders in total.
Of which 3 at the same customer, then I would like the statement to take that 1 customer with more than 1 order, divide by 44.  Giving 2.27% and not 4.55% as it would with the present statement.

For the other Users, they have as many orders as customers with at least 1 order, so stands to reason that the 3rd should have to be 0 for them or NULL.

OmarBenSalem
Partner
Partner

which information gives you how many orders were placed by a customer?

What I'm interested to know is how your data is created?

Let me try to explain what I've understood :

Employee1 has 5 distincts customers, customer 5 has placed 3 orders , so the total nb of order for Emp 1 would be 7 ! but their was only 1 customer with multiple orders, so 1/5 not 1/7? right?

Employee2 has 5 distincst, but cust 1 and 2 has each 2 orders so the % would be 2/5 not 2/7?

EMployee3 has 2 customers with 2 orders so 0/5?

If this is it, I'll try to create an inline table with this and come back later with an answer (hopefully)

ElsKnockaert
Contributor II
Contributor II
Author

That's exactly right!

OmarBenSalem
Partner
Partner

I'll assume this is it :

your measure would be something like

count(distinct {<CustID={"=count(distinct Orders)>1"}>} CustID)/count(distinct CustID)

which results in :

Capture.PNG

View solution in original post

ElsKnockaert
Contributor II
Contributor II
Author

That's it! Thank you so much!