I need to count the number of open tickets in each calendar day. I have create date and resolved date fields. Resolved date can be null.
Final desired outcome is a Quick Sight chart showing the last 12 months daily count of tickets that had create date on or before calendar day AND resolved date after the calendar day or null.
#Create a calendar from January 1, 20023 to December 31
start_date = pd.Timestamp(‘2023-01-01’, tz=UTC)
end_date = pd.Timestamp(‘2023-10-23’, tz=UTC)
calendar = pd.date_range(start=start_date, end = end_date, freq = ‘D’)
#Initialize a dictionary to store counts for each day
open_tickets_count = {day:0 for day in calendar}
#Custom function to calculate open tickets for a single row
def update_open_tickets_count(row):
ticket_create_date = row[‘ticket_create_date’]
ticket_resolved_date = row[‘ticket_resolved_date’]
#Itterate through days and update counts
for day in calendar:
if ticket_create_date <= day:
if ticket_resolved_date is pd.NaT:
#Treate as open if resolved date is null
open_tickets_count[day] +=1
elif day < ticket_resolved_date:
open_tickets_count[day] +=1
#Apply the custom function to each row
df_all.apply(update_open_tickets_count, axis = 1)
#Convert the dictionary to dataframe
result = pd.DataFrame(list(open_tickets_count.items()), columns=[‘calendar_day’, ‘open_tickets_count’])
#PLot
plt.figure(figsize=(12,6))
plt.plot(result[‘calendar_day’], result[‘open_tickets_count’], marker = ‘o’, linestyle=‘-’)
plt.title(‘Daily Backlog of Open Tickets for 2023’)
plt.xlabel(‘Date’)
plt.ylabel(‘Total Backlog Volume’)
plt.grid(True)
plt.tight_layout()
plt.show()
You can add that to your Value field well and set that aggregate to sum. Then you will need to add a date field for your x-axis field well that is aggregated to Day.
I am getting the following error when adding calculated field:
At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again.
There are couple of issues and I tried to modify your idea but still not getting there. If ticket was created before a date and if ticket was resolved after that date or resolved date is null, than it is backlog.
mmddyyyy (date) field that starts on January 1, 2023 and runs until today.
I added calculated field (backlog) using the timestamp mmddyyy when ticket is updated. Tickets data in the dataset is updated when ticket is created or resolved.
ifelse((isNull({ticket_resolved_date}) OR (dateDiff({ticket_resolved_date}, mmddyyyy) < 1)) AND (dateDiff({ticket_create_date}, mmddyyyy) >= 1), 1,0)
But the results are incorrect. When I plot mmddyyyy vs backlog, backlog ranges in 10-50 tickets whereas the expected daily backlog is in 100’s and 1000s.
I think the problem is in that for each mmddyyyy only the tickets that are updated (created or resolved) are captured. I think I need something like sumover to bring all those that are still open form days before.
I’m not sure there is a way to do this in Quicksight. You could try denseRank on your ticket created field so that it shows dates without duplicates but I have not been able to test this.
Also, I don’t believe you would be able to create a date column in Athena, you would have to perform that in S3. That being said, you could try a join in Athena if you have a related table that has a date column.
From what I have found that is not currently possible in Quicksight. Was your workaround to create a data column able to help you solve the original problem?
I used the Python code above to create calendar day and total backlog count fields and stored results in a csv in S3. Then added that as a dataset in my QS Analysis.
