Count number of open tickets in a given month based on open date and closed date

[#1]

Hi,

I am new to qlik sense, and I am working on a project where I have to count the number of open tickets in a given month. Counting the number of open tickets of the current month was easy, but now I need some help with the logic of counting number of tickets closed in x month. I have the following fields: case number, status, date opened, and date closed. I want to show the data represented in a bar chart so I would have number of opened tickets per month, removing those that have been "closed/resolved" in the month. Any help is much appreciated.

I created a master calendar and was thinking of having two tables containing "date opened' and "date closed" with the associated case number in it. But that's as far as I've gotten with the planning

case_number	status	date_opened	date_closed
00717139	Awaiting Resolution	2017-04-09	-
00717140	Resolved	2017-04-09	2017-04-10
00717141	Awaiting Resolution	2017-04-09	-
00717142	New	2017-04-09	-
00717151	New	2017-04-10	-
00717152	Resolved	2017-04-10	2017-04-10
00717153	Resolved	2017-04-10	2017-04-10
00717154	Resolved	2017-04-10	2017-04-11
00717156	Resolved	2017-04-10	2017-04-12
00717155	Pending Development	2017-04-10	-
00717157	Resolved	2017-04-10	2017-04-11
00717145	Closed	2017-04-10	2017-04-10
00717158	Resolved	2017-04-10	2017-04-10
00717159	Resolved	2017-04-10	2017-04-10
00717160	Resolved	2017-04-10	2017-04-11
00717161	Closed	2017-04-10	2017-04-10
00717163	Pending Development	2017-04-10	-
00717164	Closed	2017-04-10	2017-04-10
00717165	Resolved	2017-04-10	2017-04-11

[martinpohl]

Hello Monica,

please check my suggestion attached.

for final result check your requirements

regards

[#3]

Hi Martin, I am unable to use your formula with my fields, would you be able to take a look to see why?

Thank you!

Support Cases Draft 1(1).qvf

[martinpohl]

Hello Monica,

I've tried but it only works with few selected cases.

Reduce your calendar to one value per month (if Caldate=monthstart(Caldate))

Also, the date Formates are different in calendar and open/close fields.

Can you adjust them.

Regads

[#5]

Hi Martin,

Thank you for your reply. I have fixed the formats on my Master Calendar and restricted the date to DISTINCT Date(MonthName(TempDate),'M/D/YYYY') as mnth so it now returns 1 value for each month year combination.

Now, the date formats are the same in the master calendar and opened/closed dates. But I am still having issues with getting the expression to work.

Would you be able to take a look and see what the issue might be? I know that your formula works because it worked in the sample data set.

This is the expression currently:

sum(aggr(

if(date_opened<=monthend(Month) and (date_closed>=monthstart(Month) or not match(status,'Closed','Resolved')),1,0),case_number,Month))

not sure if "Month" here should be my master calendar field name "mnth" or "Month."

Thank you for your help!

[martinpohl]

Hello Monica,

you have to use your mnth.

You don't really need a master calendar. A master calendar is wise if you have a date and want to select a month or a year from that date. For a time period (start and end) you can't use this only with intervallmatch, but then you need start and end date, not fulled end dates are not possible.

Regards