Calculating differences between dates with conditions

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

nini88
Posts: 3
Joined: Fri May 16, 2014 12:58 pm

Calculating differences between dates with conditions

Postby nini88 » Fri May 16, 2014 1:11 pm

Hey,

I hope you can help me with my problem. I have data from various customers and their belonging orders where the cases are basically organized like this:
E-Mail, Order_ID, Date, Amount, etc. and this information is organized by Order_ID which means an E-Mail address can have several Order_Ids.

In order to apply a certain formula I need to calculate the time differences between the various orders. To be more precise I want to compute the differences between each order-date BUT only IF the E-Mail address is the same.

One Example: If a customer ordered on the 05.11.2012 and 07.03.2013 and 10.06.2013 this information is for now in three cases with the common operator e-mail address. And now I want to have the result diff1: 4 months; diff2: 3 months, etc. But under the condition that the time differences are only computed if the e-mail address is the same. And that goes for every customer with different number of orders.

Do you have any idea how I could solve this?

Thank you very much for your help!
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

Re: Calculating differences between dates with conditions

Postby GerineL » Mon May 19, 2014 8:22 am

do you have 1 line per order or multiple variables for multiple orders per client?
i.e., what does your dataset look like?
nini88
Posts: 3
Joined: Fri May 16, 2014 12:58 pm

Re: Calculating differences between dates with conditions

Postby nini88 » Mon May 19, 2014 8:44 am

Hey,

my data looks like shown in the file attached. I have in each line an order from a customer. One customer can have multiple orders and the customer is identified by his/her e-mail address (i don't know if that is so smart but for now it is like this :)). Now i need to rearrange the data in the way that i have in each line the customer with his/her order details including the different dates of order. My goal is to calculate the average inter-purchase time and the difference from today to the last order date. I hope it's understandable now. :)

Thanks for your help!
You do not have the required permissions to view the files attached to this post.
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

Re: Calculating differences between dates with conditions

Postby GerineL » Mon May 19, 2014 11:17 am

ok, what you can do here is a lag function:

- first sort on email adress
- then use lag function with if to give a new variable the date of the previous line of the date variable already in your dataset, only if the email adress of the previous one is similar.
nini88
Posts: 3
Joined: Fri May 16, 2014 12:58 pm

Re: Calculating differences between dates with conditions

Postby nini88 » Mon May 19, 2014 11:21 am

that sounds fairly simple! :)
Could you give me the syntax for that command?
thanks a lot!
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

Re: Calculating differences between dates with conditions

Postby GerineL » Mon May 19, 2014 1:41 pm

an example of such a lag function (of course change the var names etc, and create a date variable first):

if (email= lead(email)) dat2 = lead(date)+1.
execute.

Who is online

Users browsing this forum: No registered users and 1 guest

cron