Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Keitaru
New Contributor III

Need help! Calculating Monthly Outstanding Tickets Trend

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

 

4 Replies

Re: Need help! Calculating Monthly Outstanding Tickets Trend

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

Highlighted
Keitaru
New Contributor III

Re: Need help! Calculating Monthly Outstanding Tickets Trend

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

Re: Need help! Calculating Monthly Outstanding Tickets Trend

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

Keitaru
New Contributor III

Re: Need help! Calculating Monthly Outstanding Tickets Trend

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.