6 Replies Latest reply on Aug 31, 2018 6:54 AM by Roger Pouly

# Identifying new customers when they cancel their first orders

Hello everyone,

I am back here with an issue that looks simple, but I cannot seem to get it right.

Basically we currently have this formula to calculate new customers :

IF([Index Order]) = 1 THEN 'First order'
ELSEIF [Index Order] > 1 THEN 'Repeat purchase'
ELSE 'Quote'
END

Index order is linked to the customer. My issue is, sometimes customers order then cancel their order. I have a working boolean field, [Order Cancel], to identify orders that are cancelled.

I want that if the first orders are cancelled, the first non-cancelled order from the customer would be display as 'First order'.

Any insight ?

• ###### 1. Re: Identifying new customers when they cancel their first orders

Hi Roger

Understand what you are doing but don't you also have an order number for each order?  and when the order is cancelled doesn't it reference the order number?

if so you can check the order number for the first order for each customer like {fixed customer:min(order number)}  against the cancellation -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Identifying new customers when they cancel their first orders

Hi Roger,

Could you please share some sample data.

• ###### 3. Re: Identifying new customers when they cancel their first orders

Good morning Jim,

I also have an order number for each order, called 'Order Key', which is referenced when the order is cancelled.

I've never used Fixed before, but I see its value. How would you run the cancellation against it ?

• ###### 4. Re: Identifying new customers when they cancel their first orders

Good morning Meenu,

I will be able to share some sample in a short while. Will edit this post when I manage to do it.

• ###### 5. Re: Identifying new customers when they cancel their first orders

Roger

OK                  fixed min              {fixed [customer]:min(order key)}   will return the minimum order number for each customer an(and hold it a fixed value)

then when you change against the return                if [Order key] = [fixed min] then ......    end              will identify if the referenced order key is equal to the min for theat customer (ie - the first order)

Jim

2 of 2 people found this helpful
• ###### 6. Re: Identifying new customers when they cancel their first orders

That's actually perfect. Thank you Jim for your answer, and for introducing me to Fixed.

As an extra question, do you know how should I do if I want the first order to display 'First Order' (this is already what happens) but for subsequent not cancelled order, display 'Second order', 'Tenth order', etc... ?