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

John-Peddle

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 Sum - https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Cumulative-Monthly-Sum/td-p/15711

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

 

 

rado98

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:

 

Date	Type
01-01-18	Review
02-01-18	Review
03-01-18	Review
04-01-18	Review
05-01-18	Review
06-01-18	Review
01-01-18	Processed
02-01-18	Processed
03-01-18	Processed
04-01-18	Processed
05-01-18	Processed
06-01-18	Processed
07-01-18	Processed

 

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

rado98

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:

 

Date	Type
01-01-18	Review
02-01-18	Review
03-01-18	Review
04-01-18	Review
05-01-18	Review
06-01-18	Review
01-01-18	Processed
02-01-18	Processed
03-01-18	Processed
04-01-18	Processed
05-01-18	Processed
06-01-18	Processed
07-01-18	Processed

 

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

John-Peddle

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...