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
Best Answer
-
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:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Answers
-
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:
SpoilerSource 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)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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.
0 -
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.
0 -
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:
SpoilerCreated_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.
0 -
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:
SpoilerCreated_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.
0 -
Try adding a beastmode to identify the status of each issue:
CASE
WHEN `Resolved_date` IS NULL THEN 'Open'
ELSE 'Resolved'
ENDThen use this new field as the series
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Hi,
So when I do that it gives me the the total amount of issues twice (once for "Created" and once for "Resolved"
0 -
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.” -Superman0 -
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.” -Superman0 -
Nope - this is what the source looks like:
0 -
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.” -Superman1 -
Yes I'm using
CASE
WHEN `Resolved` IS NULL THEN 'Open'
ELSE 'Resolved'
END1 -
OK, then you need to place that beast mode as the series and select "no aggregation"
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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.
0 -
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:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Thank you so much for your patience and the detailed help - I got it to work correctly now!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 691 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 112 Manage
- 109 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive