Created vs Resolved chart over month

Drejz

Hi, 

I'm importing data from both Zendesk and Jira where I want to create a graph for each with number tickets/issues being created vs resolved each month. 

 

However I can't seem to make it right. 

 

For my X-axis i put the "Created" value, on the Y-axis i put the counf of ticket/issue numbers and then a series with the "Count of Resloved" however this leaves me with the correct amount of created tickets/issues but the wrong amount of Resolved ones. 

It seems like it's only counting those that are resolved the same month that they are created. 

 

Any bright ideas on how I get the x-axis to be an arbitrary date range so I can simply count the Created and Resolved for each of those dates? 

 

Kind regards,

Andreas

ST_Superman

I'm going to refer you back to my original post where I suggested some data engineering.

 

Taking your screenshot, I have tried to replicate this process for you:

I started by Collapsing the columns in an ETL:

 

 

Output Dataset

Then you can graph the data to show what you are looking for.  How many tickets were created in a month and how many were resolved:

 

ST_Superman

I'm guessing that you have two date fields in your data set.

 

You will want to "stack" the ticket data so that you have a data set that looks like this:

Spoiler

Source	Ticket #	Action	Date
Jira	11111	Opened	3/1/2019
Jira	11111	Resolved	3/20/2019
Jira	22222	Opened	3/14/2019
Jira	33333	Opened	3/15/2019
Jira	33333	Resolved	3/17/2019
Jira	44444	Opened	3/28/2019
Jira	44444	Resolved	4/3/2019
Jira	55555	Opened	4/1/2019
Zendesk	aaa	Opened	3/2/2019
Zendesk	aaa	Resolved	3/5/2019
Zendesk	bbb	Opened	3/7/2019
Zendesk	ccc	Opened	3/10/2019
Zendesk	ccc	Resolved	4/8/2019
Zendesk	ddd	Opened	3/12/2019
Zendesk	ddd	Resolved	3/15/2019
Zendesk	eee	Opened	3/20/2019
Zendesk	fff	Opened	3/21/2019
Zendesk	fff	Resolved	4/5/2019
Zendesk	ggg	Opened	4/1/2019
Zendesk	ggg	Resolved	4/6/2019
Zendesk	hhh	Opened	4/5/2019
Zendesk	iii	Opened	4/10/2019
Zendesk	iii	Resolved	4/11/2019

Then you can filter the card for the single date value and you would do your counts with a beastmode:

-- Count of Opened Tickets
count(distinct CASE WHEN `Action`='Opened' then `Ticket #` end)

-- Count of Resolved Tickets
count(distinct CASE WHEN `Action`='Resolved' then `Ticket#` end)

Drejz

Hi, 

Thank you for your reply. 

I'm afraid I didn't explain myself correctly. 

 

I'm not trying to create 1 chart with both in but simply 1 for each but I'm having the same problem in them. 

 

My data looks something like this: 

Created_date	Ticket #	Resolved_date
5 Apr 2019 02:44:00 AM	111111	5 Apr 2019 02:44:00 AM
5 Mar 2019 02:44:00 AM	222222	12 Apr 2019 02:44:00 AM
1 Apr 2019 02:44:00 AM	333333
1 Jan 2019 02:44:00 AM	444444

 

 

So the only way I know if an issue has been resolved is if the date is filled, otherwise it's just blank. 

 

 

So when I use the created_date as my x-axis it will give the correct amount of issues created within the current month but for the resolved part it will only count those that are both created and resolved in the same month. 

Drejz

Hi, 

Thank you for your reply. 

I'm afraid I didn't explain myself correctly. 

 

I'm not trying to create 1 chart with both in but simply 1 for each but I'm having the same problem in them. 

 

My data looks something like this: 

Created_date	Ticket #	Resolved_date
5 Apr 2019 02:44:00 AM	111111	5 Apr 2019 02:44:00 AM
5 Mar 2019 02:44:00 AM	222222	12 Apr 2019 02:44:00 AM
1 Apr 2019 02:44:00 AM	333333
1 Jan 2019 02:44:00 AM	444444

 

 

So the only way I know if an issue has been resolved is if the date is filled, otherwise it's just blank. 

 

 

So when I use the created_date as my x-axis it will give the correct amount of issues created within the current month but for the resolved part it will only count those that are both created and resolved in the same month. 

Drejz

Hi, 

Thank you for your reply. 

I'm afraid I didn't explain myself correctly. 

 

I'm not trying to create 1 chart with both in but simply 1 for each but I'm having the same problem in them. 

 

My data looks something like this: 

Spoiler

Created_date	Ticket #	Resolved_date
5 Apr 2019 02:44:00 AM	111111	5 Apr 2019 02:44:00 AM
5 Mar 2019 02:44:00 AM	222222	12 Apr 2019 02:44:00 AM
1 Apr 2019 02:44:00 AM	333333
1 Jan 2019 02:44:00 AM	444444

 

 

So the only way I know if an issue has been resolved is if the date is filled, otherwise it's just blank. 

 

 

So when I use the created_date as my x-axis it will give the correct amount of issues created within the current month but for the resolved part it will only count those that are both created and resolved in the same month. 

Drejz

Hi, 

Thank you for your reply, but I'm afraid I wasn't clear in my description. 

 

I don't want to create a chart with both but rather one for each, and I'm having the same problem with both. 

 

My data looks something like this: 

Spoiler

Created_date	Issue #	Resolved_date
2019-01-01 20:00:00	111111	2019-01-01 23:00:00
2018-12-12 21:00:00	222222
2018-11-12 02:00:00	333333	2019-01-02 23:00:00

So when I used Created_date as my x-axis and count of Issue # for the y-axis and then a series with count of Resolved_date then I get the correct amount of created for the month but for resolved I only get those that were created and resolved within that month. 

 

So the only way I know if an issue has been resolved would be if the resolved_date is empty. 

ST_Superman

Try adding a beastmode to identify the status of each issue:

CASE 
WHEN `Resolved_date` IS NULL THEN 'Open'
ELSE 'Resolved'
END

Then use this new field as the series

 

Drejz

Hi,

 

So when I do that it gives me the the total amount of issues twice (once for "Created" and once for "Resolved" 

 

 

ST_Superman

Use another beast mode for y axis:

COUNT(DISTINCT `Issue #`)

ST_Superman

I'm not sure what is going on with your visualization.

 

If each row of your source data has a created date, and issue #, and a resolved date (which is null if the ticket is opened), then the beastmode that I provided should only give one status per issue #.

 

Does your data source have two rows of data for each ticket?  one row when it is created (with a null resolved date) and another row of data once the issue is resolved?

Drejz

Nope - this is what the source looks like: 

ST_Superman

Then what is the beastmode that you are using to classify the status of each ticket?  Are you using the beastmode I provided?

 

CASE 
WHEN `Resolved` IS NULL THEN 'Open'
ELSE 'Resolved'
END

Drejz

Yes I'm using 

CASE 
WHEN `Resolved` IS NULL THEN 'Open'
ELSE 'Resolved'
END

ST_Superman

OK, then you need to place that beast mode as the series and select "no aggregation"

Drejz

No aggregation on the Beastmode series but I still get odd values in the graph. 

 

 

Here is a picture showing that there are 52 issues resolved in April 

 

However when I make the setup with the created date as my x-axis, count of issue key as the y and the beastmode you suggested as the series with no aggregation i get the below. 

 

I think the issue is that I'm using the created date as the x-axis but I don't know how else I'm supposed to get them spread over the months. 

ST_Superman

I'm going to refer you back to my original post where I suggested some data engineering.

 

Taking your screenshot, I have tried to replicate this process for you:

I started by Collapsing the columns in an ETL:

 

 

Output Dataset

Then you can graph the data to show what you are looking for.  How many tickets were created in a month and how many were resolved:

 

Drejz

Thank you so much for your patience and the detailed help - I got it to work correctly now!