Calculating with custom date fields

Peter Dierckx March 1, 2021

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
Ilze Leite-Apine
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 15, 2021

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
AUG Leaders

Atlassian Community Events