Created vs Resolved chart over month

Options
Drejz
Drejz Member
in Charting & Analyzer

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

  • ST_Superman
    ST_Superman Domo Employee
    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:6.png

    I started by Collapsing the columns in an ETL:

     

     

    1.png2.png3.png

    Output DatasetOutput 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

     

Answers

  • ST_Superman
    ST_Superman Domo Employee

    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)
  • Drejz
    Drejz Member

    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_dateTicket #Resolved_date
    5 Apr 2019 02:44:00 AM1111115 Apr 2019 02:44:00 AM
    5 Mar 2019 02:44:00 AM22222212 Apr 2019 02:44:00 AM
    1 Apr 2019 02:44:00 AM333333 
    1 Jan 2019 02:44:00 AM444444 

     

     

    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
    Drejz Member

    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_dateTicket #Resolved_date
    5 Apr 2019 02:44:00 AM1111115 Apr 2019 02:44:00 AM
    5 Mar 2019 02:44:00 AM22222212 Apr 2019 02:44:00 AM
    1 Apr 2019 02:44:00 AM333333 
    1 Jan 2019 02:44:00 AM444444 

     

     

    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
    Drejz Member

    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_dateTicket #Resolved_date
    5 Apr 2019 02:44:00 AM1111115 Apr 2019 02:44:00 AM
    5 Mar 2019 02:44:00 AM22222212 Apr 2019 02:44:00 AM
    1 Apr 2019 02:44:00 AM333333 
    1 Jan 2019 02:44:00 AM444444 

     

     

    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
    Drejz Member

    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. 

  • ST_Superman
    ST_Superman Domo Employee

    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

  • Drejz
    Drejz Member

    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

  • ST_Superman
    ST_Superman Domo Employee

    Use another beast mode for y axis:

    COUNT(DISTINCT `Issue #`)
  • ST_Superman
    ST_Superman Domo Employee

    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
    Drejz Member

    Nope - this is what the source looks like: 

    data.png

  • ST_Superman
    ST_Superman Domo Employee

    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
    Drejz Member

    Yes I'm using 

    CASE 
    WHEN `Resolved` IS NULL THEN 'Open'
    ELSE 'Resolved'
    END
  • ST_Superman
    ST_Superman Domo Employee

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

  • Drejz
    Drejz Member

    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 

    total resloved april.png

     

    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. 

    graph total.png

     

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

    I started by Collapsing the columns in an ETL:

     

     

    1.png2.png3.png

    Output DatasetOutput 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

     

  • Drejz
    Drejz Member

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

This discussion has been closed.

Categories