Dynamic Closure Date Categorization

Question & Answer
, , , , , ,
1

The current categorization system in the issue tracking system assigns closed issues to the month of their creation rather than their closure. For example, if an issue is created in January but closed in March, it’s included in January’s statistics due to the control being based on creation date. However, the desired functionality is to allocate closed issues to the month they were actually closed. I have issuecreated date and Issueclosed date…

2

Hi @hellosai321
maybe you can share some more information? Like current calculation or something.
BR

3

Hi @ErikG ,

I have a dashboard that relies on JIRA tickets, containing fields such as issue ID, issue creation date, and issue closure date. I’ve implemented a month field based on the issue creation date, allowing me to categorize the number of created and closed tickets within each month. However, I encountered a scenario where a ticket was created in January but closed in March. In this case, I want to reflect that one ticket was created in January and one ticket was closed in March. Currently, my calculation includes the closed count under January, as the month calculation is based on the creation date. I’m seeking a solution where the closed count is allocated to the month in which the issue was closed, irrespective of its creation date. Below are the calculations I’m using

Month=extract(‘MM’, {issuecreateddate}) and year =extract(‘YYYY’, {issuecreateddate})

4

Hi @ErikG, to achieve your goal in Amazon QuickSight, you need to modify your calculations so that the created tickets are counted based on their creation date and the closed tickets are counted based on their closure date. Here’s how you can approach this:

  1. Create a separate field for the creation month and year.
  2. Create a separate field for the closure month and year.
  3. Use these fields to calculate the counts for created and closed tickets.

Here’s how you can do it step by step:

Step 1: Create Calculated Fields for Creation Date

  1. Month Created:
createMonth = extract('MM', {issuecreateddate})
  1. Year Created:
createYear = extract('YYYY', {issuecreateddate})

Step 2: Create Calculated Fields for Closure Date

  1. Month Closed:
closeMonth = extract('MM', {issuecloseddate})
  1. Year Closed:
closeYear = extract('YYYY', {issuecloseddate})

Step 3: Create Calculated Fields for the Counts

  1. Created Tickets Count:
createdTicketsCount = countIf({issueid}, not isNull({issuecreateddate}))
  1. Closed Tickets Count:
closedTicketsCount = countIf({issueid}, not isNull({issuecloseddate}))

Step 4: Visualize the Data

  1. Create a Table or Pivot Table:
  • Add the fields createMonth and createYear to the rows.
  • Add the field createdTicketsCount to the values.
  1. Create another Table or Pivot Table:
  • Add the fields closeMonth and closeYear to the rows.
  • Add the field closedTicketsCount to the values.

By doing this, you can correctly visualize the number of tickets created in each month and the number of tickets closed in each month, regardless of their creation date.

Example:

Suppose you have the following data:

issueID issuecreateddate issuecloseddate
1 2024-01-15 2024-03-20
2 2024-01-20 2024-01-25
3 2024-02-10 2024-03-05

The calculations will result in:

  • Created Tickets:
    • January 2024: 2 tickets
    • February 2024: 1 ticket
  • Closed Tickets:
    • January 2024: 1 ticket
    • March 2024: 2 tickets

Putting It All Together

In Amazon QuickSight:

  1. Go to your dataset and create the calculated fields as described above.
  2. Use these fields in your analysis to correctly categorize the created and closed tickets by their respective months.

This approach ensures that your dashboard accurately reflects the creation and closure dates of tickets, solving the issue where the closed count was incorrectly allocated based on the creation date.