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?
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:
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:
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:
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)
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:
|2019-01-01 20:00:00||111111||2019-01-01 23:00:00|
|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.
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
Use another beast mode for y axis:
COUNT(DISTINCT `Issue #`)
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?
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