13 Replies Latest reply: Apr 20, 2017 6:25 PM by Monica Labrador

# Count of number of open tickets a month

Hi, I am trying to build an app which counts the number of open tickets in a month. I am having trouble thinking through how to calculate cumulative remaining tickets opened. Any help or direction is appreciated.

The chart would summarize like below:

Month, number of open tickets:

Jan-2017 4

Feb-2017 5

Mar-2017 5

 case_number date_opened date_closed 1 1/2/17 1/5/17 2 1/7/17 1/30/17 3 1/8/17 3/1/17 4 1/10/17 - 5 2/2/17 2/8/17 6 2/5/17 2/20/17 7 2/8/17 3/5/17 8 3/1/17 3/6/17 9 3/3/17 -

• ###### Re: Count of number of open tickets a month

If your data is as follow:

case_number , open in months

3, Jan

3, Feb

3, Mar

3, Janv,Feb,Mar

You'll just need to have a table :

as dimension:

Open in months

as a measure:

count(distinct {1}case_number)

If you want 3 seperated KPI:

count(distinct{<'open in months'={'Jan'}>} case_number)

count(distinct{<'open in months'={'Feb'}>} case_number)

count(distinct{<'open in months'={'Mar'}>} case_number)

• ###### Re: Count of number of open tickets a month

Hi Omar,

I deleted the last column, since "open in months" field is not included in my data-set, but I thought it would be helpful as a reference.

• ###### Re: Count of number of open tickets a month

Base on Stefan's suggestion, I would say that the script should be altered as follow:

cases:

load * , month(ReferenceDate)&'-'&year(ReferenceDate) as ReferenceMonth;

load *, Date( date_opened + IterNo()- 1 ) as ReferenceDate

While

IterNo() <= date_closed - date_opened + 1;

load case_number  , date(date#(date_opened,'M/D/YY')) as date_opened,

date(date#(date_closed,'M/D/YY')) as date_closed;

case_number, date_opened, date_closed

1, 1/2/17, 1/5/17

2, 1/7/17, 1/30/17

3, 1/8/17, 3/1/17

4, 1/10/17, 1/10/17

5, 2/2/17, 2/8/17

6, 2/5/17, 2/20/17

7, 2/8/17, 3/5/17

8, 3/1/17, 3/6/17

9, 3/3/17, 3/3/17

];

as dimension : referenceMonth

As measure: count(distinct {1} case_number)

• ###### Re: Count of number of open tickets a month

Hi Omar,

The suggestion Stefan put forward assumes that there is a "date_closed" for all records, but there are not. I tried using your example but I am facing an issue with open cases with no closed dates being unaccounted for (showing up in a gray bar in my chart).

I have attached the qvf.

• ###### Re: Count of number of open tickets a month

Monica, you can replace missing values in date_closed with a value that is appropriate for your requirement, e.g. Today():

cases:

load * , month(ReferenceDate)&'-'&year(ReferenceDate) as ReferenceMonth;

load *, Date( date_opened + IterNo()- 1 ) as ReferenceDate

While

IterNo() <= Alt(date_closed,Today()) - date_opened + 1;

• ###### Re: Count of number of open tickets a month

Hi Stefan,

I think I am closer to solving the problem. A few issues to point out, 1. I added this line as you recommended, but now I have thousands more records than I had before, does Alt() function make duplicate records, I should have a total of 3,800 open cases, but the total has now jumped up to 322,777. Also, it is interesting that the total of "open" records in April has doubled than what I was expecting to see in the chart, it should be 3,800 and now it has jump up to 6.6k. Would you be able to take a look at this revised app?

• ###### Re: Count of number of open tickets a month

case_number is now your key field, and you should never count a key field without a DISTINCT qualifier.

If you need to count non-distinct in a specific table, use another field in that table.

count({<status-={'Resolved', 'Closed', 'Duplicate'}>} distinct case_number)

• ###### Re: Count of number of open tickets a month

BTW, if you need only reference values per month, not on date granularity, you can reduce the link table to month granularity, like

Input:

INLINE [

ID, Start, End

1, 30.01.2017, 11.03.2017

2, 12.12.2016,

];

RESIDENT Input

While  Monthstart(Alt(End,Today(1))) >= Monthstart(Start,iterno()-1);

• ###### Re: Count of number of open tickets a month

Stefan, can you explain this further?

are you saying to change the referencedate to referencemonth? The chart will be viewed on the month level, not on a date level.

• ###### Re: Count of number of open tickets a month

Thank you, I updated to distinct case numbers, and have changed the logic to indicate that a case is considered "open" when it has null date_closed.

count(distinct{<case_number={'=Len(Trim(date_closed))=0'}>}case_number)

• ###### Re: Count of number of open tickets a month

change my script as follow:

cases:

load * , month(ReferenceDate)&'-'&year(ReferenceDate) as ReferenceMonth;

load *, Date( date_opened + IterNo()- 1 ) as ReferenceDate

While

IterNo() <= ALT(date_closed,date_opened) - date_opened + 1;

load case_number  , date(date#(date_opened,'M/D/YY')) as date_opened,

date(date#(date_closed,'M/D/YY')) as date_closed;

case_number, date_opened, date_closed

1, 1/2/17, 1/5/17

2, 1/7/17, 1/30/17

3, 1/8/17, 3/1/17

4, 1/10/17, 1/10/17

5, 2/2/17, 2/8/17

6, 2/5/17, 2/20/17

7, 2/8/17, 3/5/17

8, 3/1/17, 3/6/17

9, 3/3/17, 3/3/17

];

as dimension : referenceMonth

As measure: count(distinct {1} case_number)

DON'T FORGET THE DISCTINCT to eliminate the duplication (if there is)

• ###### Re: Count of number of open tickets a month

Omar,

IterNo() <= ALT(date_closed,date_opened) - date_opened + 1;

This gets me closer to my expected value. I expect to see 3,909 open cases in April (based off of the KPI logic which says that a case is considered open when it does not have a date_closed value), but I am getting 3,630 open cases in the chart. What do you think the variance is?

Also, I am having a difficult time re-ordering/sorting the dates in the chart.

Will you be able to take a look and see what could be causing the issues?

Thank you!

• ###### Re: Count of number of open tickets a month

Have a look at

Creating Reference Dates for Intervals

This should do pretty much what you require.