Counting values across 2 date fields in 1 chart

user14867
user14867 Member
in Charting

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.

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user14867
    user14867 Member

    Nice! I appreciate the creative solution, thank you.

  • user14867
    user14867 Member

    Also can you clarify "join the table twice"?

    Thanks in advance

     

  • GrantSmith
    GrantSmith Coach

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user14867
    user14867 Member

    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.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    @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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
This discussion has been closed.

Categories