How to create a calculated field that tracks the overall status change for each project ?

[FelipeQR]

Hi Alteryx Community,

I'm very new to Alteryx and I was hoping someone could give some guidance on how to create a calculated field that tracks the overall status change for each project in my dataset.

My dataset has the following key columns:

• Status report date: The date when the project status was reported
• Project ID: A unique identifier for each project
• Overall Status: A categorical variable that indicates the current status of the project. It can be one of these values: “(1) On Track”, “(2) At Risk”, or “(3) Off Track”.
• Multiple SR: A flag that indicates whether there were multiple status reports for the same project on the same date
• MAX_SR_Version: A numeric variable that shows the version number of the most recent status report for each project on each date
• SR_Version: 4 digit counter/identifier appended to the status report date. 

I want to create a new calculated field called Status change that shows how the overall status of each project has changed over time. The possible values for this field are:

• “Worsening”: If the project status has changed from “(1) On Track” to either “(2) At Risk” or “(3) Off Track”
• “Improving”: If the project status has changed from “(2) At Risk” or “(3) Off Track” to “(1) On Track”
• “No Change”: If the project status has not changed

How can I create this calculated field using Alteryx?

Any help or guidance would be appreciated. Thank you.

[DataNath · Accepted solution]

Hey @FelipeQR, how does something like this look? I've removed a load of the fields just for ease of seeing the calculation in action - obviously if you implement this then just remove the Select tool! As you have a status date field, we can just sort, within each Project, the statuses in ascending order and then use a Multi-Row Formula tool to look up a row and compare the statuses. I've also added a 'Project Started' flag for the first entry of each Project as there's no prior status to compare to but hopefully this helps! Please shout up if you have any further questions or anything!

Only thing that comes to mind instantly is the final Project you can see in the screenshot. As we're going from (4) to (1) i.e. Unknown to 1 then I've assumed this can just stay as 'No Change'? The criteria above only stated changes between 1 to 3.