Multiple stages in cycle and delta for time between

[Anonymous]

Hi All,

This is a little complex from a situational stand point... I am trying to determine how long a person is aging in a certain status(Old Status Description), before they are changed to another status(New Status Description). There is no natural progression in the status that a person may go through to make things a little more complicated, a person can go from Null to Active, to Inactive, back to Active, then to Pending and so on....  I am trying to determine the delta, time it takes, for a person to change from one status to another, each change has a time stamp associated with it. I am sure there is a much easier way to do this, below is what I have thus far... Also I can not get any of the calc fields (calc1, calc2) to generate time difference.

Any help is greatly appreciated!

Calcs:

Load UniqueID,
IF([Old Status Description]='Null',StatusChangeDate) as [App Submission Date],
IF([Old Status Description]='Active',StatusChangeDate) as [Old Status Active],
IF([Old Status Description]='Active with Audit',StatusChangeDate) as [Old Status Active with Audit],
IF([Old Status Description]='Application',StatusChangeDate) as [Old Status Application],
IF([Old Status Description] ='Holding',StatusChangeDate) as [Old Status Holding],
IF([Old Status Description]='Inactive',StatusChangeDate) as [Old Status Inactive],
IF([Old Status Description]='Inactive with Audit',StatusChangeDate) as [Old Status Inactive with Audit],
IF([Old Status Description]='Pending',StatusChangeDate) as [Old Status Pending],
IF([Old Status Description]='Pending with Audit',StatusChangeDate) as [Old Status Pending with Audit],
IF([New Status Description]='Active',StatusChangeDate) as [New Status Active],
IF([New Status Description] ='Active with Audit',StatusChangeDate) as [New Status Active with Audt],
IF([New Status Description]='Application',StatusChangeDate) as [New Status Application],
IF([New Status Description] ='Holding',StatusChangeDate) as [New Status Holding],
IF([New Status Description] ='Pending',StatusChangeDate) as [New Status Pending],
IF([New Status Description] ='Pending with Audit',StatusChangeDate) as [New Status Pending with Audit],
IF([New Status Description]='Inactive with Audit',StatusChangeDate) as [New Status Inactive with Audit],
IF([New Status Description]='Inactive',StatusChangeDate) as [Finish Date]
Resident TravelerStatusChangeHistory;


Calc1:
Load UniqueID,
[Finish Date]-[App Submission Date] as CycleTime
Resident Calcs;

Calc2:
Load UniqueID,
[New Status Active]-[App Submission Date] as [App to Active Time]
Resident Calcs;

Calc3:
Load UniqueID,
[New Status Active with Audt]-[App Submission Date] as [App to Active with Audit Time]
Resident Calcs;

Calc4:
Load UniqueID,
[New Status Application]-[App Submission Date] as [App to Application Time]
Resident Calcs;

Calc5:
Load UniqueID,
[New Status Holding]-[App Submission Date] as [App to Holding Time]
Resident Calcs;

Calc6:
Load UniqueID,
[New Status Inactive with Audit]-[App Submission Date] as [App to Inactive with Audit Time]
Resident Calcs;

Calc7:
Load UniqueID,
[New Status Pending]-[App Submission Date] as [App to Pending Time]
Resident Calcs;

Calc8:
Load UniqueID,
[New Status Pending with Audit]-[App Submission Date] as [App to Pending with Audit Time]
Resident Calcs;

[swuehl]

UniqueID is identifying the user, so there can be more than one record for UniqueID or does it identify a unique record?

Could you upload some lines of sample data?

In general, your Calc tables are done on a record base, so they only work when there are values on the same record for both fields involved (which is unlikely for most records). You can potentially use a GROUP BY UniqueID with conditional aggregations on your timestamps to overcome this.

But I would rather start with something like this (assuming UniqueID identifies your user):

Calcs:

Load UniqueID,

[Old Status Description],

[New Status Description],

If(Previous(UniqueID) = UniqueID, StatusChangeDate - Previous( StatusChangeDate ) ) as IntervalBetweenChanges

Resident TravelerStatusChangeHistory

ORDER BY UniqueID, StatusChangeDate asc;

[Anonymous]

Hi swuehl,

Yes, the unique id identifies the user. I included a sample of the data that I am working with. 

Unique ID	StatusChangeDate	Old Status Description	New Status Description
21	36:45.7	NULL	Application
21	46:51.5	Application	Active
21	47:08.6	Active	Inactive
61	52:47.9	NULL	Pending
61	30:14.4	Pending	Active
61	00:00.0	Active	Active with   Audit

Thank you for the help!

[Gysbert_Wassenaar]

See Criteria on load Calculation.

Next time please don't create a duplicate discussion. Have a little more patience.

[Anonymous]

Will do, thank you