Analysis Suggestions

[Christopher_Lawrence]

Greetings All,

I am attempting to calculate the time it takes a user to get through the full process of interviewing. I have attached following example data to this chain. 

Some context: A user is invited to a job, which consist of 3 interviews they must take to complete the process. Each interview is assigned to a stage within a job (So if stage says 1, it is the first interview, if stage is 2, it is the second interview, if stage says 3, it is the third interview). To make things easier, I have also passed in a value that provides the total stages the corresponding job has (in this case 3, as this job only has 3 interview stages). My goal is to basically find the difference from the final stage's CompletedOn date and the first stage's CreateOn date to get the time it took the candidate to complete the full process, and get an average/median.

However, I now am a bit lost. Would it make sense to have a lengthy set analysis in a regular table (showcasing hostname, jobID and the average/median time it took to complete the process per jobID) or is there an easier way of doing this inside of the load editor? The example only provides 2 candidates with 3 interview for the same job, however the real data will have more candidates attached to jobs that might have 2 stages or 5 stages. If it makes more sense to do logic in the load editor, how would you got about doing it?

[Or]

I'm not sure I've got it quite right, but if I understood you correctly, I think this should work in script:

Load *

Where StageCount=2; // This is so we only count completed processes and not ones that are partway through or were never completed

Load JobID, UserID,  Max(CompletedOn) - Min(CreatedOn) as TotalTime, count(Stage) as StageCount

From YourTable

Where Stage = 1 OR Stage = TotalStages // Keep only the first and last stages

Group by JobID, UserID; // Group by whatever fields we need, I assumed JobID and UserID but could be anything