Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can't seem to figure this out. I have weekly (Friday) snapshot data on over 500 Projects with a Status field. My data is one .xlsx file that I keep concatenating to every week. My data spans all of 2019 to date (1/24/20).
There are over 15 statuses, but I am trying to count how many projects have switched to Complete status in 2020. There are currently 42 projects that have a Complete status, but most of them have become Complete in 2019.
How can I count when Status field changes to Complete (from any other status)? Should/can I do this at chart level, or in load script?
Sample data of one project: (altered for simplicity)
PROJECT ID | DATE | STATUS | COMPLETION DATE (NEW FIELD) |
AD231 | 11/29/2019 | Testing | - |
AD231 | 12/6/2019 | Testing | - |
AD231 | 12/13/2019 | Awaiting Confirmation | - |
AD231 | 12/20/2019 | Awaiting Confirmation | - |
AD231 | 12/27/2019 | Awaiting Confirmation | - |
AD231 | 1/3/2020 | Awaiting Confirmation | - |
AD231 | 1/10/2020 | Complete | 1/10/2020 |
AD231 | 1/17/2020 | Complete | - |
AD231 | 1/24/2020 | Complete | - |
That project should be in my count, since it became Complete on 1/10/2020. This project should NOT be counted....
PROJECT ID | DATE | STATUS | COMPLETION DATE (NEW FIELD) |
AD188 | 11/29/2019 | Awaiting Confirmation | - |
AD188 | 12/6/2019 | Awaiting Confirmation | - |
AD188 | 12/13/2019 | Complete | 12/13/2019 |
AD188 | 12/20/2019 | Complete | - |
AD188 | 12/27/2019 | Complete | - |
AD188 | 1/3/2020 | Complete | - |
AD188 | 1/10/2020 | Complete | - |
AD188 | 1/17/2020 | Complete | - |
AD188 | 1/24/2020 | Complete | - |
.....Since it became Complete in 2019.
Thank you!!
I just realized what would be perfect for me. Ideally if I can create a Field in the load script that copies the Date when Complete appears for the first time.
New field could be called Completion Date, and be null except for the one date where Complete appears the first time.
So in top chart my Completion Date would have 9 null values and have 1/10/2020 on the 7th row where it became Complete.