Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

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_numberstatusdate_openeddate_closed
00717139Awaiting Resolution2017-04-09-
00717140Resolved2017-04-092017-04-10
00717141Awaiting Resolution2017-04-09-
00717142New2017-04-09-
00717151New2017-04-10-
00717152Resolved2017-04-102017-04-10
00717153Resolved2017-04-102017-04-10
00717154Resolved2017-04-102017-04-11
00717156Resolved2017-04-102017-04-12
00717155Pending Development2017-04-10-
00717157Resolved2017-04-102017-04-11
00717145Closed2017-04-102017-04-10
00717158Resolved2017-04-102017-04-10
00717159Resolved2017-04-102017-04-10
00717160Resolved2017-04-102017-04-11
00717161Closed2017-04-102017-04-10
00717163Pending Development2017-04-10-
00717164Closed2017-04-102017-04-10
00717165Resolved2017-04-102017-04-11
5 Replies
martinpohl
Valued Contributor II

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

Hello Monica,

please check my suggestion attached.

for final result check your requirements

regards

Not applicable

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

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
Valued Contributor II

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

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

Not applicable

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

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
Valued Contributor II

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

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

Community Browser