Report on Count of Records closed where "Actual End Date" is before "Planned End Date"

RC2
Tera Contributor

‎04-18-2022 09:14 AM

Hi everyone. 

I need to build a dashboard with some sort of visual showing how many changes were completed ahead of schedule. So where the "Actual End Date" was before the "Planned End Date". End goal is to get a percentage or count per month. 

I can export the data to Excel and do a Pivot that way, but I would prefer NOT to do it manually every month and instead put a chart or data on a Dashboard in ServiceNow. But I can't quite figure out how to do it!!

Basically, I can't find a way to identify XXX records where the Actual End Date < Planned End Date. I'm not even sure if ServiceNow can do this...

Labels:
0 Helpfuls
  • 1,244 Views
5 REPLIES 5

Tricia Cornish
ServiceNow Employee
ServiceNow Employee

‎04-18-2022 10:04 AM

Hi RC - Yes, it's quite easy to do this.  Use filter conditions in your report or your indicator and use the "is more than" (or is less than) x time and then choose your comparative date field. Even though the lowest time frame seems to be hours, Zero is a valid value and will find those records that are minutes ahead of their schedule. As you can see in my example, in my test 2, the actual end is only 2 minutes ahead of planned end. 

find_real_file.png

Preview file
96 KB
Preview file
124 KB
Preview file
130 KB
0 Helpfuls

RC2
Tera Contributor
In response to Tricia Cornish

‎04-18-2022 11:50 AM

So, I got it working, sort of. 

I have one chart that is "total changes" (all after a specific date). And I added a data set with the additional filter and conditions you have above. Now I need to find a way to visually show them as a percentage of each other...some way? Any idea if this is possible?

RC2
Tera Contributor

‎04-18-2022 11:16 AM

Awesome! I was trying this before and I wasn't sure if the "zero hours before" worked or not because I was worried it would pick up ones with exact time stamp. But it appears it does not!

Now to find a way to get a percentage overall. I got one data set and added a second data set with all changes....

0 Helpfuls

Tricia Cornish
ServiceNow Employee
ServiceNow Employee
In response to RC2

‎04-22-2022 07:36 AM

For that calculation, you would use Performance Analytics. If you are using change requests, you would create one indicator (using the Changes.Closed indicator source) that has filter conditions for the actual end date before planned end date. You could use Number of closed change requests as the second indicator, then create a formula indicator that calculates the %: Completed before Planned / Number of Closed Change Requests . 

0 Helpfuls