3 Replies Latest reply on May 17, 2017 12:14 PM by Lillian Aj

# Determining Order Status Using Prior Order Date as Marker

I am trying to calculate the "PRIOR" order date in order to determine if a customer is New, Regained or Active.   I am using the Lookup function to try and achieve that however it is not so effective with me using a "ATTR" aggregation in the formula.  Can anyone help me to refine this calculation so that I can see for each customer their prior order date (to the last order date)?

• ###### 1. Re: Determining Order Status Using Prior Order Date as Marker

You don't need a table calc here, just use MAX([Order Date]). You can also work out some date math to let you know if this is a new purchase:

IF MIN([Order Date]=MAX([Order Date]) THEN 'New Customer' ELSE 'Prior' END

--Shawn

• ###### 2. Re: Determining Order Status Using Prior Order Date as Marker

Hi Shawn,

I can get the "New" order status.  It is the Regained customers that is the challenge right now.   I need to be able to look at the Order Date prior to the Last order date to see if the last order was placed within the last 18 months.

So a regained customer is any account that has history with us but did not place any order with us since 18 months of the last order.  I am trying to determine the MAX([Order Date]) -1 record back.

Thanks,

Sandi

• ###### 3. Re: Determining Order Status Using Prior Order Date as Marker

Hi Sandi,

Were you able to figure this out? If yes, how did you go about it? I am have similar challenge.

Thanks