New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor III

aggr in set analysis compare with set value

Hello everybody

I have an issue with

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

• set value

1 Solution

Accepted Solutions
MVP

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 :

6 Replies
MVP

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

Contributor III
Author

Of course! I hope this helps:

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.

MVP

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)

Contributor III
Author

That's exactly right!

MVP

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 :

Contributor III
Author

That's it! Thank you so much!

Tags
Community Browser