Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Calculating with custom date fields

Edited

due to historical input we had to create 2 custom fields in Jira (start date and end date), because de start and/or end date was different from the timestamp.

So we created in EazyBi next:

[chosen start date]

IIf(IsEmpty([Measures].[Issue Start date (manualy)]),
[Measures].[Time stamp start date] ,
[Measures].[Issue Start date (manualy)])

and

[chosen end date]

IIf(IsEmpty([Measures].[Issue End date (manualy)]),
[Measures].[Time stamp end date] ,
[Measures].[Issue End date (manualy)])

Then we calculated the lead time [chosen end date]-[chosen start date]

What we now want to do is to calculate the average lead time for all the issues of a specific month they were closed, but the problem is that we don't get any data when we select all issues in "Individual members". We do get data when we select the issues in "all hierarchy level members".
So what I want is aggregated lead time / issues

Futhermore the custom date (is a [measure] not a [date]) doesn't respond when I use the time dimension.

 

what I need in the end is:

  nov/20dec/20jan/21feb/21
AAvg Time    
 # issues    
BAvg Time    
 # issues    





1 answer

1 accepted

0 votes
Answer accepted

Hi Peter

Yes, for individual issues this approach would work fine.

Though, when you want to get the issue count, as well as average, those conditions must be applied for each issue, and then calculate the average or total count over the set of those issues. It could be quite slow.

If you need to use those fields regularly in reports, the best solution would be creating two JavaScript calculated custom fields - Start date and End date - to retrieve one date as a start date and one as the end date, based on the values in the manual and automatic date fields for each issue. Read here how to do that: JavaScript calculated custom fields 

Then you may want to import those newly created custom fields Start date and End date as measures. As a result, you would get measures Issues with Start date and Issues with End date. Use Issues with End date for # issues row. Read more here: Measures with custom date fields 

 

For the average calculation, you may want to iterate through all issues having this End date in the given time period, and then calculate the average day's difference between the start and end dates:

 

Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod([Issue].CurrentMember.get("End date"),
[Time].CurrentHierarchyMember)
AND
[Measures].[Issues with End date] > 0),
DateDiffDays(
[Issue].CurrentMember.get("Start date"),
[Issue].CurrentMember.get("End date")
))

 

Or, instead of the calculation, you may create the third JavaScript custom field (let's name it Duration) to calculate the duration from the start to the end date and import this field as a measure. You would get a bunch of measures related to the duration: Totals of custom number fields 

Then the average calculation would be simple, based on imported measures ([Measures].[Issues with end date] would be from End date custom field, while [Measures].[Duration with end date] - from both, Duration and End date):

 

CASE WHEN
[Measures].[Issues with end date]>0
THEN
[Measures].[Duration with end date]
/
[Measures].[Issues with end date]
END

 

Ilze / support@eazybi.com

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Apps & Integrations

🍻🍂Apptoberfest Update: Upcoming Virtual Events 🎉

Hello Community! I hope you've been enjoying the 🍂Apptoberfestivities🍂 (I know I have!) The event is heating up next week with a series of virtual events that we're calling the 🍻🍂Partner App ...

380 views 3 17
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you