Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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!
See Criteria on load Calculation.
Next time please don't create a duplicate discussion. Have a little more patience.
Will do, thank you