Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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 :
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
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.
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)
That's exactly right!
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 :
That's it! Thank you so much!