Hello,
I have a dataset of support tickets with open_date and close_date fields. I want to calculate the number of tickets that were open in each month.
For example, if a ticket was opened in September and closed in November, it should be counted as open in September, October, and November, but not in December.
How can I achieve this in QuickSight?
Thank you
Hi @InbalK ,
I would try creating a calculated formula to try and flag whether tickets are open or closed in a given month. Here a formula you can try:
ifelse(
$CheckMonth >= {open_date} AND
(isNull({close_date}) OR $CheckMonth <= {close_date}),
‘Open’,
‘Closed’
)
The $CheckMonth is a date parameter which would be connected to a date control. This formula should tell you if a ticket is open or closed based on the month that is selected in the date parameter. Let me know if this helps!
Hi, thank you!
I’m not sure this solves my case, so let me explain more clearly.
I have this table that currently shows the number of tickets opened in each month. The issue is that tickets which remain open are only counted in their opening month.
What I need instead is to count tickets in every month they were open. For example, if a ticket was opened in October and is still open, it should also appear in November and December (until it is closed).
Thank you
Hi @InbalK ,
As the row combinations for a given ticket and the potential months in between creation and close date do not exist in your current dataset; there’s not really a default function or a calculated field that can be created that provides this type of count request.
The best work around in this scenario would most likely be to create a second dataset that strictly has dates (by month for your scenario). Then you can join that to your original dataset so that the month fields now exist to be worked with. I would try to refer to a previous community post that is somewhat similar to your case (Count of active events over time - Q&A - Amazon Quick Community).
Hope this helps!
Hi @InbalK,
Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.
If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
Thank you
