topic Re: Need help! Calculating Monthly Outstanding Tickets Trend in App Development

Need help! Calculating Monthly Outstanding Tickets Trend

Keitaru — Sat, 16 Nov 2024 20:59:14 GMT

Hi Experts need some help here.

I'm trying to find out what my monthly outstanding open tickets are I've attached a working sample and a sample data dump of the what i'm using.

Looking to plot a combo chart that will display both the outstanding Open tickets along side both Newly Created and Closed Tickets.

However I've yet to be able to figure out how to calculate my monthly outstanding count.

I've thought about using the expression below however wasn't able to get the correct numbers when teleing against my other reporting.

aggr(Rangesum(Below(Count(Distinct {<[Status*]={'Opened'}>} [Ticket ID])- Count(Distinct {<[Status*]={'Closed'}>} [Ticket ID]), 0, rowno(Total))),Date_MY)

The data should look close to these numbers below.

Month	Outstanding
Aug - '17	827
Sep - '17	901
Oct - '17	947
Nov - '17	952
Dec - '17	1053
Jan- '18	1114
Feb-'18	1149
Mar '18	1412
Apr '18	1,508
May '18	1,522
Jun '18	1495
Jul '18	1585
Aug'18	1693
Sep - '18	1864
Oct - '18	2,037
Nov - '18	1,872
Dec - '18	1,907
Jan- '19	1,679
Feb-'19	1,533
Mar-19	1,497

Re: Need help! Calculating Monthly Outstanding Tickets Trend

sunny_talwar — Thu, 25 Apr 2019 11:36:24 GMT

How are you getting these numbers? Would you be able to elaborate this by taking 1-2 examples from the sample provided?

Re: Need help! Calculating Monthly Outstanding Tickets Trend

Keitaru — Thu, 25 Apr 2019 16:35:58 GMT

Those numbers were provided by another team who is doing the reporting via excel.

Re: Need help! Calculating Monthly Outstanding Tickets Trend

sunny_talwar — Thu, 25 Apr 2019 16:39:37 GMT

Would you be able to explain the output picking an example Ticket ID?

Re: Need help! Calculating Monthly Outstanding Tickets Trend

Keitaru — Fri, 26 Apr 2019 05:25:18 GMT

if you look at the sample i've provided, the idea is to plot against combo chart the running count of outstanding open Ticket numbers against newly created and Closed Tickets.

Initially i've tried doing things such as

aggr(Rangsum(below(count({<Status={'Open'}>}[Ticket ID])),0,rowno(total)),Month)

against MMM-YY to count the number of outstanding open tickets in that month. And if the ticket continues to remain open it will be added to the next month and so forth.

however that didnt work and I applied the same logic against a crosstable to see if i could get the same results or not.