Hi, I wonder if an expert here could help me with a formula i've been struggling with for some time.
I am trying to create some statistics from data queried from our Ticketing system, and want to get a total number of Resolved cases per user, however if a case has a resolution cancelled, and then is resolved for a 2nd time, both resolves are counted in the statistics which can sometimes make it look like the least effective members of the team (who have their resolutions cancelled multiple times) are actually the most effective (highest number of resolves)
The relevant data in the spreadsheet is as follows:
A B C D E
INCIDENT ID | DATE OF ACTION | OPERATION | RESOLVED BY | FINAL RESOLVE? |
INC00001 20-08-2015 14:00 RESOLVE John Smith
INC00001 21-08-2015 16:00 CANCEL Joe Bloggs
INC00001 21-08-2015 17:00 RESOLVE Joe Bloggs
INC00002 22-08-2015 09:30 RESOLVE Joe Bloggs
INC00003 23-08-2015 10:00 RESOLVE John Smith
INC00003 23-08-2015 11:00 CANCEL Joe Bloggs
INC00003 23-08-2015 12:00 RESOLVE John Smith
INC00003 24-08-2015 09:00 CANCEL Jane Doe
INC00003 24-08-2015 10:00 RESOLVE Joe Bloggs
The current report i am running, using a SUMPRODUCT would calculate 3 resolves for John Smith and 3 for Joe Bloggs but I would like it to show 0 for John Smith as all his resolutions were later cancelled.
I thought the easiest way would be to add the "FINAL RESOLVE?" column and have it state Yes or No if it is the last time there was a RESOLVE operation for that INCIDENT ID and then use that in my SUMPRODUCT instead of the OPERATION field but I can't figure out what formula I could use to achieve this.
Any help would be greatly appreciated.
Bookmarks