Question on open ticket count

[jeffv]

Hi all,

Just wondering if someone can point me to a thread of accomplishing something with Alteryx.   Say I have a table with a ticket, opendt, closedt.  It seems easy to calculate how many tickets per day were opened or closed but how can I calculate how many are open on a particular day.  I need a count of tickets on a day that are between open and closed dts.  Not sure the tools to calculate this.

Ideas?

Thanks,

Jeff

[AngelosPachis · Accepted solution]

Hi @jeffv ,

What you can do is use a generate rows tool to generate the dates that each ticket is open, so generate all dates between the opendt and closedt columns.

Then you can use a summarise tool to count how many tickets appear for each date (group on Date, count ticket)

Then you will end up with a list of dates that you had an open ticket. But what happens for the dates that you didn't have any ticket open?

To add those dates in your dataset, you can generate another date list, extending from the minimum opendt to the maximum closingdt. Then with a join tool, you can join on the date field and replace the null values with a zero.

Not sure if I had to include the workflow in this post, but you can give it a try and if you think you need any help at some point, you can use the workflow.

Hope that helps,

Angelos

[jeffv]

Thanks....  it worked.  Solved the issue I am facing.  Now to absorb and understand the idea behind why it works is my next step.

Thanks for your insight and help!

Jeff

[AngelosPachis]

Feel free to reach out if you have any questions @jeffv, happy that I was able to help.

Regards,

Angelos