Created vs. Resolved minus Canceled

ConnorW
Giga Contributor

I'm trying to create a report that can show the number Incidents and Requested Items Created on a date versus the number of items resolved on that date (So I can show a week or a month or just a certain time period as I change the filter). 

 

I'm using the Time Series Column Type of report.  I create a dataset using Task [task] and then use the assignment group and created on a certain date.  I then Trend By Created.  I then configure using Standard Calendar/Date/Count. 

 

The only problem is if I try to filter out created items that were canceled, they are not removed, so the amount created is much larger than it should be.  I then create a new dataset in that report for the resolved based on resolved date/time and assignment group and then Trend By resolved date/time.  Those stacked together give me the report I'm looking for but obviously not accurately.

 

When I use the separate tables of Requested Item [sc_req_item] and Incident [incident], I can get those two columns to remove any canceled tickets using the filter of State is not Canceled.  But that does not present it as a grand total of created items versus resolved items.

 

I have also tried separate task type in the Task [task] table but still the same results of canceled tickets that were created.

 

Thanks in advance.

 

 

1 REPLY 1

Its_Azar
Tera Guru

Hey there @ConnorW 

 

Certainly! Here's a forum-style response:

 

I'd suggest combining Incidents and Requested Items into a single dataset and applying a filter to exclude canceled tickets. This will help your report accurately reflects the number of tasks created and resolved, without counting canceled tickets.

Once your dataset is configured correctly, adjust the trend settings in your Time Series report to accurately trend the created and resolved tasks over time based on the created/resolved date.

 

If this helps kindly accept the response thanks much.

Best regards,
Azar

 

☑️ If this helped, please mark it as Helpful or Accept Solution so others can find the answer too.




Kind Regards,

Mohamed Azarudeen Z

Developer @ KPMG

 Microsoft MVP (AI Services), India