Merging two date fields into a single date

Question & Answer
,
1

Hello team, I would like to know if it is possible the following:

I need to create a table where I can see by month the number of tickets that were created and resolved.

These tickets can be resolved in the same month in which they were created, but there are others where they were resolved the following month.

image
image820×436 29.3 KB

I need to create something similar to the following table where I have the month and identify how many were created and approved in order to create a ticket resolved ratio.

image

2

Hi @DannyV,

I’ve built an example which works for my data, and may use more calculated fields than needed, but it explains the process.

I created 2 calculated fields that truncate the created_date and resolved_date fields to Month level:
truncCreatedDateMM
truncDate('MM',{created_date})

truncResolvedDateMM
truncDate('MM',{resolved_date})

Then I created 2 calculated fields that count the number of resolved tickets for this month and following months:
thisMonth
ifelse(truncCreatedDateMM=truncResolvedDateMM,1,0)

followingMonths
ifelse(truncCreatedDateMM<truncResolvedDateMM,1,0)

Then I use 2 calculated fields to count the number of resolved and created tickets using
createdCnt
count({epic_key},[truncCreatedDateMM])

resolvedCnt
sum(thisMonth)+sum(followingMonths)

Finally, I calculate the ratio as follows:
finalRatio
resolvedCnt/max(createdCnt)

Here are screenshots of my field wells and the resulting table based on my dummy data:
image

image

Let me know if this solution works for you.

Many Thanks,
Andrew

3

@abacon almost there, thanks for the help.

I used the same data I showed above, but February doesn’t match based on your calculation. The created count is 3 when it should be 2.

image
image829×671 45.5 KB

4

@DannyV checking back in. Is this still an issue?

Do all your other months work?

5