-
Re: Count number of open tickets in a given month based on open date and closed date
Martin Pohl Apr 14, 2017 3:31 PM (in response to Monica Labrador)Hello Monica,
please check my suggestion attached.
for final result check your requirements
regards
-
Comm257168.qvf 176.0 K
-
Re: Count number of open tickets in a given month based on open date and closed date
Monica Labrador Apr 14, 2017 6:51 PM (in response to Martin Pohl )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!
-
Re: Count number of open tickets in a given month based on open date and closed date
Martin Pohl Apr 15, 2017 5:07 AM (in response to Monica Labrador)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
-
Support Cases Draft 1(1).qvf 52.0 MB
-
Re: Count number of open tickets in a given month based on open date and closed date
Monica Labrador Apr 19, 2017 12:45 PM (in response to Martin Pohl )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!
-
Support Cases Draft 1(1)(1).qvf 16.0 MB
-
Re: Count number of open tickets in a given month based on open date and closed date
Martin Pohl Apr 20, 2017 2:52 AM (in response to Monica Labrador)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
-
-
-
-