Created vs Resolved chart over month

Reply
White Belt

Created vs Resolved chart over month

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


Accepted Solutions
Black Belt

Re: Created vs Resolved chart over month

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:6.png

I started by Collapsing the columns in an ETL:

 

 

1.png2.png3.png

4.pngOutput 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:5.png

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Black Belt

Re: Created vs Resolved chart over month

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
SourceTicket #ActionDate
Jira11111Opened3/1/2019
Jira11111Resolved3/20/2019
Jira22222Opened3/14/2019
Jira33333Opened3/15/2019
Jira33333Resolved3/17/2019
Jira44444Opened3/28/2019
Jira44444Resolved4/3/2019
Jira55555Opened4/1/2019
ZendeskaaaOpened3/2/2019
ZendeskaaaResolved3/5/2019
ZendeskbbbOpened3/7/2019
ZendeskcccOpened3/10/2019
ZendeskcccResolved4/8/2019
ZendeskdddOpened3/12/2019
ZendeskdddResolved3/15/2019
ZendeskeeeOpened3/20/2019
ZendeskfffOpened3/21/2019
ZendeskfffResolved4/5/2019
ZendeskgggOpened4/1/2019
ZendeskgggResolved4/6/2019
ZendeskhhhOpened4/5/2019
ZendeskiiiOpened4/10/2019
ZendeskiiiResolved4/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)

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Created vs Resolved chart over month

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_dateIssue #Resolved_date
2019-01-01 20:00:001111112019-01-01 23:00:00
2018-12-12 21:00:00222222 
2018-11-12 02:00:003333332019-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. 

Black Belt

Re: Created vs Resolved chart over month

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

 

1.png


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Created vs Resolved chart over month

Hi,

 

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

 

 

JIRA_Test_-_Issues_-_Domo.png

Black Belt

Re: Created vs Resolved chart over month

Use another beast mode for y axis:

COUNT(DISTINCT `Issue #`)

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Black Belt

Re: Created vs Resolved chart over month

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?


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Created vs Resolved chart over month

Nope - this is what the source looks like: 

data.png

Black Belt

Re: Created vs Resolved chart over month

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

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Created vs Resolved chart over month

Yes I'm using 

CASE 
WHEN `Resolved` IS NULL THEN 'Open'
ELSE 'Resolved'
END
Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information