Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | - |
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)
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.
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)
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.
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;
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?
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)
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)