Counting values across 2 date fields in 1 chart

Reply
Highlighted
Yellow Belt

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.


Accepted Solutions
Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Red Belt

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



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

Nice! I appreciate the creative solution, thank you.

Highlighted
Yellow Belt

Also can you clarify "join the table twice"?

Thanks in advance

 

Highlighted
Red Belt

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



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Yellow Belt

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.

Highlighted
Red Belt

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



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.