Counts for 2 separate dates columns in a single month (SQL Dataflow)

Good afternoon. We had previously requested something very similar and accomplished it via Magic ETL but want to do the same thing, only this time using a MySQL dataflow instead of Magic ETL (previous thread - split & reappend a dataset). We've looked for documentation but unable to find anything; can anyone direct us to the documentation, if it does exist, or possibly explain this process using MySQL instead of Magic ETL?

 

If additional information is needed, please don't hesitate to ask! Thanks

Best Answer

  • MarkSnodgrass
    Answer ✓

    @John-Peddle Ahh.. I understand now. Here's what you would want to do in SQL. Use the transforms to create 2 table, one holding all of the created dated, with an additional column called DateType and has a hard coded value of Created. The 2nd table would contain all of the resolved dates with an additional column called DateType and has a hard coded value of Resolved. You would then UNION these together in the output dataset. It would look something like this. 

    1. Create "created" table

    SELECT `Created Date` AS TicketDate, 'Created' AS DateType
    FROM `sample`
    WHERE `Created Date` IS NOT NULL

    2. Create "resolved" table

    SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
    FROM `sample`
    WHERE `Resolved Date` IS NOT NULL

    3. UNION together in output dataset

    SELECT *
    FROM `created`

    UNION

    SELECT *
    FROM `resolved`

    You could add your other columns that you referenced in your sample when you are creating your two tables. Obvioulsy they need to be in the same order, etc. 

    The Domo card will automatically group the data for you by month when you tell it what to group by in the date filter. This should give you a nice grouped bar chart when the series is TicketType to show the number of created vs. resolved.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hey @MarkSnodgrass, appreciate your response and think I may have presented our issue inaccurately so I'd like to provide some sample data to see if that helps clarify the ask.

     

    Started to work on a dataflow that breaks out the input dataset into 2 new tables, and attempted to union them to get the results needed but it's not working, though we believe we're on the right track at least.

     

    Sample Data:Screen Shot 2019-10-09 at 6.45.43 PM.png

     

     

     

     

     

     

     

     

     

     

     

    In a card, we'd like to show the percentage of issues resolved vs those created by month. So for September, we had 10 issues created (created date) and 6 isses resolved/completed, which would be 60%.

     

    Problem is, using either of the existing date fields (Created or Resolved) on the X-Axis obviously won't work due to a number of reasons, such as

    1) issues created in August & completed in September won't appear if using CREATED DATE

    2) issues created in September but not completed until October will show up if using CREATED DATE

    3) issues resolved in September would not include those created in prior months if using RESOLVED DATE

     

    Does that make sense? From what we've read it sounds like we'd need to create 2 new tables from the inoput dataset, but need to better understand where we're going wrong because what we have so far is not correct.

     

    Any thoughts? 

  • MarkSnodgrass
    Answer ✓

    @John-Peddle Ahh.. I understand now. Here's what you would want to do in SQL. Use the transforms to create 2 table, one holding all of the created dated, with an additional column called DateType and has a hard coded value of Created. The 2nd table would contain all of the resolved dates with an additional column called DateType and has a hard coded value of Resolved. You would then UNION these together in the output dataset. It would look something like this. 

    1. Create "created" table

    SELECT `Created Date` AS TicketDate, 'Created' AS DateType
    FROM `sample`
    WHERE `Created Date` IS NOT NULL

    2. Create "resolved" table

    SELECT `Resolved Date` AS TicketDate, 'Resolved' AS DateType
    FROM `sample`
    WHERE `Resolved Date` IS NOT NULL

    3. UNION together in output dataset

    SELECT *
    FROM `created`

    UNION

    SELECT *
    FROM `resolved`

    You could add your other columns that you referenced in your sample when you are creating your two tables. Obvioulsy they need to be in the same order, etc. 

    The Domo card will automatically group the data for you by month when you tell it what to group by in the date filter. This should give you a nice grouped bar chart when the series is TicketType to show the number of created vs. resolved.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @MarkSnodgrass - thanks so much, your solution worked exactly as we'd hoped but...

     

    As is the case with most BI requests we receive, the requirements changed a bit and after some work we were able to work with what you gave us and made the necessary adjustments in the dataflow, and used a Line + Bar Chart to show both totals (created & resolved) as well as the percentage between the two. 

     

    Thanks so much for all your help on this request, it's greatly appreciated! Smiley Happy