Hi,
I've got a problem, and I think the solution can't be very hard. But... I can't get the job done! Hopefully someone can help me on this one!
I've got a dataset with 3 rows:
Date - CustomerID - Status
The dataset contains a set of dates (from 2008 until now, per month) for every customer ID. For every month, the status (0, 1, 2, 3, etc) has been set for every customer. Example:
01-01-08 - Customer1 - 0
01-02-08 - Customer1 - 0
01-03-08 - Customer1 - 1
01-04-08 - Customer1 - 0
01-05-08 - Customer1 - 1
01-06-08 - Customer1 - 2
01-07-08 - Customer1 - 1
etc., until the date 01-08-10 is reached, and on that moment, Customer 2 is presented.
What I want, is to add another column, which reflects the status ("Hold" if the status was the same the month before and the status > 0, "Forward" if the status is higher than the month before, "Back" if the status is lower than the month before (except for status 0) and "None" if the status reaches "0" when the status a month ago was higher than 0. If the status is 0 in a specific month and was also 0 a month ago, then I like to add the word "Zero" to the new column).
So, I was trying things with "if first.customerID", but with this approach you can only get the figures for the first and last customerID.
Can anyone help me on this one? Thanks in advance!!!