Calculate duration based on status change of a record

[#1]

Hi,

Please assist with the syntax to calculate the following scenario:

• I have a database with sales opportunities.
• The opportunity name is the unique identifier for each record
• Each record has a creation date
• Each record has a state (open, suspended, abandoned, lost, won)
• Each record has a "snapshot date".  This date is based on me taking an extract of the data each week, saving that in Excel and in my load script I assign a snapshot date to that import file to import the Excel sheet with the date for each record. 
• So I have a new Excel sheet every week, and my load script will load all the excel sheets in that folder, each with it's own snapshot date assigned to all the records.  Every week I add a new excel sheet to the folder
• So a record will always be in state "open" when it is created and in that week will have a particular snapshot date, lets say 01-03-2015
• At some point in future-snapshot date, this record will change in status (either to suspended, or lost, or won, or abandoned.  It can change again after that in future - from lost back to won for example). 
• I want to track the duration from 'open' state, to any of the other states, particularly "won".

Attached is a sample table of the data I'm referring to

The result will hopefully be a table/chart showing me how long it takes the sales rep to go from "open" to "won" (or any of the other states).

Hope the info is clear.

cherio

[#2]

Hi Manus

for each record

LOAD

OpportunityName,

Max(SnapShotDate) - Min(SnapShotDate) should give the ellapsed time between the first (Open) and last (status) date

RESIDENT Table

group by OpportunityName;

best regards

Chris

[#3]

Hi Christian, tx for the help.

NO, unfortunately I'm still lost. First off I could not get the Load script right, it gave me a load error.  So I simply used the "max" - "min" in an expression and it did give me a value but this seem to be the total max and minus the total minimum, one answer is like 323 days.  In my sample upload spreadsheet you'll see a status can be "OPEN" for a few dates (weeks) and then "WON" after 4 dates (weeks) and that record stays with the status of WON in the database for all the weeks after that.  So the "max" calculation takes the very last record when it should have taken the 4th week date and calculated it there.  Hope this makes sense