Counting values across 2 date fields in 1 chart

I have a data set and I'm trying to count by date (YY-MM) the number of tickets opened and the number of tickets closed.
If I look at 3 fields:
Ticket#
OpenDate
ClosedDate

Charting # of tickets by open date works okay.

If I add in ClosedDate (whether by a second line or if I use a pivot table), the # of tickets by ClosedDate is wrong.
It seems like there's an issue because I don't have an independent date field and the bucketing is based on whether I use the OpenDate or ResolveDate.

For example, in August 2019 (19-08), 360 cases were opened and 324 cases were closed.
However, my chart or table shows 338 cases were closed.

Not sure what I'm missing, any guidance is appreciated.

Find more posts tagged with

Accepted answers

GrantSmith

What I've done in the past is join the table twice, once based on the open date, and again based on the closed date. Then I join those two split data sets back together based on Open Date = Closed Date and rename the count field to be Open Count and Closed Count (or whatever you'd like to call it). Then you can graph based on your singular date field but get the number of open and closed tickets on a specific day.

Hope that helps.

GrantSmith

@user14867 - You're getting a bunch of extra records because you're doing a large many to many cross join.

What you'll need to do is reduce your dataset first before you do your join so you only have one record. It should just be two simple group by widgets.

They will both spawn from your original dataset.

The first Group By should be based on the open date with it's output connecting to Join / #3 in your image. Name the output Open Count

The second Group By would be based on the close date connecting to the Join / #6 in your image. Name the output Closed Count

Both Group By widgets would count the distinct number of ticket number values.

Let me know if that works or if you need additional clarification.

All comments

GrantSmith

Hope that helps.

user14867

Nice! I appreciate the creative solution, thank you.

user14867

Also can you clarify "join the table twice"?

Thanks in advance

GrantSmith

Here's an example:

Note: This is assuming you have one record per date otherwise you'll get duplicate records. If you want on a per record invoice you'd need to include another primary key in your joins than just the date.

Screen Shot 2020-02-10 at 4.21.37 PM.png

user14867

Thank you for the example and details.

Unfortunately, I'm having trouble with getting this to function as described.
Depending on how I tweak, I either get too few or way too many records.
As per your note "This is assuming you have one record per date otherwise you'll get duplicate records," I do have multiple records per date. I think the only other field I can join on is the ticket ID (unique per row in the Ticket Data source.
I'm more interested in records per year/month if that helps though; I see the Calendar object has "Ym" which is a numeric value of yyyymm; maybe this helps?
I've attached the image of my workflow with the items numbered:

1. Calendar object from Domo
2. Ticket Data source
3. Left join (Calendar) to Ticket data where "dt" = "open_date"
4. Using columns: dt; TicketNum; open_date
5. Filter "TicketNum" is not null (to limit # of records).
6. Left join (Calendar) to Ticket data where "dt" = "resolved_date"
7. Only using columns: dt; TicketNum; resolved_date
8. Filter "TicketNum" is not null (to limit # of records).
9. Inner join dt=dt [this is something I don't understand and I'm likely incorrect].
10. Using columns: TicketNum; dt; open_date; resolve_date.

In this example, my dataset of 112,000 records goes into the millions and I have to cancel the workflow.

Workflow image.png

GrantSmith

@user14867 - You're getting a bunch of extra records because you're doing a large many to many cross join.

What you'll need to do is reduce your dataset first before you do your join so you only have one record. It should just be two simple group by widgets.

They will both spawn from your original dataset.

The first Group By should be based on the open date with it's output connecting to Join / #3 in your image. Name the output Open Count

The second Group By would be based on the close date connecting to the Join / #6 in your image. Name the output Closed Count

Both Group By widgets would count the distinct number of ticket number values.

Let me know if that works or if you need additional clarification.