Multiple date columns (count) from single dataset - using 12 month calendar NOT from dataset?

I've read several posts on things simialr to this, but not sure that this exact issue has ever been brought up or of it's even possible. We've been running into an issue when trying to create a Line + Grouped Bar chart that would require data from two separate date columns (ReviewedDate and ResolvedDate) in the same dataset. These dates are not dependent on one another, so it may have been reviewed in May but not resolved until June so they need to appear in the specific month they were either initiated (Reviewed/Issue Identified) or completed (Resolved). We've attempted several ETL's so far with no luck.

 

We have several other date columns available, but they don't relate directly to either of these columns, nor do they provide a "real-time" X Axis option that would show only what was initiated or resolved in a particular month - the reason we want/need to show both is that we also want to determine the difference of Issues Reviewed/Identified vs. Issues Resolved/Completed to get a better handle of what's "rolling over" to the following month, and just how far this department is behind with regards to their work. We have a ticket submitted w/Domo Support but so far they've either provided us information on that is for something else (i.e. MoM Comparison), or the resolution presented doesn't work. 

 

Because of the inexperience on our end, if at all possible it would be preferred to do this by ETL rather than SQL, but we can probably adapt any "generic" transform examples to the specific scenario provided. We've included a series of screenshots that should help better visualize how the raw data is received, and the two existing cards that we'd like to bring together into a single view. Any thoughts or suggestions would be greatly appreciated, and let us know if we can provide any additional information that would help accommodate this request, if it's something that can even be done. Thanks in advance for any assistance you can provide.

 

Just a couple of the previous topics found that seem to be looking for something similar to what we want, but not exactly the same:

Cumulative Monthly Sumhttps://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Cumulative-Monthly-Sum/td-p/15711

Group multiple dateshttps://dojo.domo.com/t5/Card-Building/Group-multiple-dates/td-p/19843

 

 

Screen Shot 2018-07-16 at 2.33.57 PM.pngScreen Shot 2018-07-16 at 2.39.14 PM.pngScreen Shot 2018-07-16 at 12.25.45 PM.pngScreen Shot 2018-07-16 at 12.26.16 PM.pngScreen Shot 2018-07-16 at 12.26.47 PM.png

Best Answer

  • rado98
    rado98 🟠
    Accepted Answer

    If what you are after is to have the two graphs "side by side" what you minght be able to do is the following:

     

    Split the dataset and then reappend it so that you end up with somethign like this:

     

    DateType
    01-01-18Review
    02-01-18Review
    03-01-18Review
    04-01-18Review
    05-01-18Review
    06-01-18Review
    01-01-18Processed
    02-01-18Processed
    03-01-18Processed
    04-01-18Processed
    05-01-18Processed
    06-01-18Processed
    07-01-18Processed

     

    From there you should be able to count processed vs reviewed in same month.

Answers

  • Hey @rado98, thanks for the reply! We were doing a split and append, and it wasn't working. Since gone back through it step-by-step and through trial & error found where the issue was (the added constants, column combines, and append were ok but we erred in the very first step: column select), and it now works as you explained and we'd hoped it would! Appreciate your time and assistance...