so I have a set of sample data, attached. I need to to some analysis, i.e. calculate lag time between current Status & previous status(by status type), calc bonus hrs by mgr, etc.
I can easily accomplish some of this in a pivot, but unfortunately, the data set contains both statuses in each single record. Should I build a pivot off this data as it, or would I be better off duplicating each record & include each status type in its own record - thereby having only one status in each row. for example the first record would be split to become:
Before:
Id_______ Current Status___ Date___ Previous Status__ Date2
52979___Assessor Complete__ 3/13/2014__ Assessor Open__ 3/10/2014
After:
Id Current Status Date
52979 Assessor Complete 3/13/2014...
52979 Assessor Open 3/10/2014...
My other issue would be that I don't want to double count the Hours for each ID in my data(if I split up the records as suggested above).
I added a measure in last column to calculate time lag & some example pivots are included. Feedback or any other suggestions appreciated.
I can easily accomplish some of this in a pivot, but unfortunately, the data set contains both statuses in each single record. Should I build a pivot off this data as it, or would I be better off duplicating each record & include each status type in its own record - thereby having only one status in each row. for example the first record would be split to become:
Before:
Id_______ Current Status___ Date___ Previous Status__ Date2
52979___Assessor Complete__ 3/13/2014__ Assessor Open__ 3/10/2014
After:
Id Current Status Date
52979 Assessor Complete 3/13/2014...
52979 Assessor Open 3/10/2014...
My other issue would be that I don't want to double count the Hours for each ID in my data(if I split up the records as suggested above).
I added a measure in last column to calculate time lag & some example pivots are included. Feedback or any other suggestions appreciated.
