nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Ticket open duration in days

Krzysztof Achinger

Hello EAP Team,

I am trying to show a table with tickets' open duration in days. The column should show days between Issue Created At date and now. Is it possible, is there an easier way to achieve that?

I was looking to add a column that stores now date but that did not seem to be possible.

Accepted answers

Tracy Chow_f2gtzj

You can add a formula column with a custom formula using the SQLite datediff function and the relative date variable {TODAY}.

Screen Shot 2022-03-04 at 12.29.39 PM.png

All comments

Adam Nichols (DISH)

We have run into the same issue and had to create a custom script

Krzysztof Achinger

No way! How do you create custom script and where do you store the data if that is not too much to ask? Any additional pointers will help.

Adam Nichols (DISH)

Here is the script we used in Automation for Jira

Krzysztof Achinger

Right, all my previous data issues I was also resolving with Automation for Jira. I was hoping that I will not have to do it in Atlassian Analytics.

@Matt David @Cat Africa hope you are reading

Cat Africa

Hey, Krzysztof! You can use the {TODAY} relative date variable to get today’s date.

For what you’re trying to do, you can do the following (assuming you’ve made your initial query with the "Created At" column):

1. Add a Formula column step.

2. Select Custom as the formula type.

3. Enter {TODAY} as the formula.

4. Select Save. You should now have a new column where all the rows are today’s date.

5. Add another Formula column step.

6. Select Date difference as the formula type.

7. For the parameters, first select the Created At column, then select the new today column you created, then select Day as the time unit.

8. Select Save. This should give you the date difference you’re looking for.

Hope that helps a bit!

Tracy Chow_f2gtzj

You can add a formula column with a custom formula using the SQLite datediff function and the relative date variable {TODAY}.

Screen Shot 2022-03-04 at 12.29.39 PM.png

Krzysztof Achinger

It did the trick. I was actually playing with {TODAY} after reading the documentation but I did not know I need to use Custom formula for it to work. Thank you.

Krzysztof Achinger

It did help, thank you.

Kamal Rajpal_8s0ghx

Is there any way to get this calculation done in JQL, which I am using to create filter in JIRA

Cat Africa

Hi, @Kamal Rajpal! It may be better to ask this question in Jira's Community group: https://community.atlassian.com/t5/Jira/ct-p/jira

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

date

analytics