Count of number of open tickets a month

[#1]

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	-

[OmarBenSalem]

If your data is as follow:

case_number , open in months

3, Jan

3, Feb

3, Mar

instead of

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)

[swuehl]

Have a look at

Creating Reference Dates for Intervals

This should do pretty much what you require.

[#4]

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.

[OmarBenSalem]

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;

load * Inline [

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

];

Then in your table chart:

as dimension : referenceMonth

As measure: count(distinct {1} case_number)

[#6]

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.

[swuehl]

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;

[#8]

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?

[OmarBenSalem]

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;

load * Inline [

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

];

Then in your table chart:

as dimension : referenceMonth

As measure: count(distinct {1} case_number)

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

[swuehl]

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)