Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hello Monica,
please check my suggestion attached.
for final result check your requirements
regards
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!
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
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!
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