Issues created out of office hours

martins.vanags · October 12, 2017, 12:13pm

There are a couple of ways how to calculate the number of issues created outside office hours.

First, let us look at an example to find issues created before 9 am or after 6 pm in eazyBI.
To do this, you can define a created hour breakdown dimension with advanced settings:

[jira.customfield_hodc]
name = "Hour of Day Created"
data_type = "integer"
dimension = true
javascript_code = '''
issue.fields.customfield_hodc = parseInt(issue.fields.created.match(/T(\d\d):/)[1]);
'''

Further, select the new dimension for import from the data import screen as a new dimension. You will be able to create the following report and even highlight the most active and passive times of the day:

Further, you can create a calculated member in Measure that sums the total issues created outside working hours (before 9 am and after 6 pm):

Sum(
  Filter(
    Except([Hour of Day Created].[Hour of Day Created].Members,
    [Hour of Day Created].[(none)]), 
  Val([Hour of Day Created].CurrentHierarchyMember.Name) < 9 OR 
  Val([Hour of Day Created].CurrentHierarchyMember.Name) >= 18
), [Measures].[Issues created])

Secondly, there is another option without importing the additional Created hour dimension. While this would also work for hours only, this approach can be slower and is suggested only if the working hours are not full, e.g., 9 AM to 5:30 PM.

In such a case, please create a new calculated member in the Measures dimension using the following code:

NonZero([Measures].[Issues created] - -- total created issues minus created during working h
Count(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateInPeriod(
    [Issue].CurrentMember.get('Created at'),
    [Time].CurrentHierarchyMember) AND -- created date in the period
  [Measures].[Issues created] > 0 AND -- issue is created in selected report context
  ((Hour(DateParse([Issue].CurrentMember.get('Created at'))) >= 9 AND -- created hour is after 9
    Hour(DateParse([Issue].CurrentMember.get('Created at'))) < 17 -- created before 17
  ) OR (
    Hour(DateParse([Issue].CurrentMember.get('Created at'))) = 17 AND -- if the hour is 17
    Minute(DateParse([Issue].CurrentMember.get('Created at'))) < 30 -- then created is before 17:30
  ))
)))

Cheers!

HelenG · May 3, 2019, 3:08pm

This would be really useful, however I can’t get it to work without timing out. Any suggestions?

martins.vanags · May 8, 2019, 4:28am

Hi,

You could try enabling the “Nonempty” cross-join for rows dimensions

Martins / eazyBI support

Izabella_Cikalova · July 2, 2021, 8:09pm

martins.vanags:

[jira.customfield_hour_of_day_created]
name = "Created hour"
data_type = "integer"
dimension = true
javascript_code = '''
var createDate = new Date(Date.parse(issue.fields.created));
var hours = createDate.getHours() - (createDate.getTimezoneOffset() / 60) ;
issue.fields.customfield_hour_of_day_created = hours;
'''

Hi, I would like to create the same report with issues closed in office hours and out of office hours. Please, could you advise how to achieve this

martins.vanags · July 5, 2021, 7:27am

@Izabella_Cikalova

Try importing from issue.fields.resolutiondate to get the hours from resolution date timestamp.

Martins / eazyBI support

Fra · June 14, 2022, 2:57pm

Hi Martins
how can i find out about the tickets created only during the weekend?

martins.vanags · June 15, 2022, 6:03am

@Fra

Try creating new calculated measure using this formula:

Aggregate({
[Week Day].[Saturday],
[Week Day].[Sunday]}
,
[Measures].[Issues created]
)

That would calculate the issues created on Sundays and Saturdays.

Martins / eazyBI

Fra · June 15, 2022, 9:06am

Thank you @martins.vanags for you help.

jayplayd · May 9, 2023, 5:37am

Hi @martins.vanags,
I am trying to get this solution to work based on the start date but so far I am not successful.
What would be the correct syntax for the required start date hour dimension?

Thank you very much!

[jira.customfield_hodc]
name = "Hour of Day Created"
data_type = "integer"
dimension = true
javascript_code = '''
issue.fields.customfield_hodc = parseInt(issue.fields.created.match(/T(\d\d):/)[1]);
'''

martins.vanags · May 9, 2023, 5:49am

@jayplayd

Most likely the start date is stored as customfield_NNNNN in jira (replace NNNNN with custom field ID).
Locate the custom field ID in Jira for “start date” and then use it in the Javascript.

[jira.customfield_hodsd]
name = "Hour of Day Start date"
data_type = "integer"
dimension = true
javascript_code = '''
issue.fields.customfield_hodsd = parseInt(issue.fields.customfield_NNNNN.match(/T(\d\d):/)[1]);
'''

Martins / eazyBI

jayplayd · May 9, 2023, 6:33am

Thanks for the quick reply. I changed the customfield definition accordingly but now I get an error when importing:

Hour of Day Start Date

[jira.customfield_hods]
name = “Hour of Day Start Time”
data_type = “integer”
dimension = true
javascript_code = ‘’’
issue.fields.customfield_hods = parseInt(issue.fields.customfield_10944.match(/T(\d\d):/)[1]);
‘’’

The field configuration looks the same as the created date field though:

grafik

Do you have an idea where the error comes from?

Thank you so much!

martins.vanags · May 9, 2023, 6:45am

@jayplayd

The error is because of issues where the start date is not entered.

Try additional validation to the script.

[jira.customfield_hodsd]
name = "Hour of Day Start date"
data_type = "integer"
dimension = true
javascript_code = '''
if(issue.fields.customfield_10944){
issue.fields.customfield_hodsd = parseInt(issue.fields.customfield_10944.match(/T(\d\d):/)[1]);
}
'''

Martins / eazyBI

jayplayd · May 9, 2023, 12:46pm

Hi @martins.vanags,
this works like a charm.
Thank you again - you made someones day today!
Best regards
Jay

Leandimoll90 · March 8, 2024, 2:27pm

Hi @martins.vanags ,

If I want to try the same report but for issues resolved outside business hours then I get an error when using issue.fields.resolutiondate

It states:
Cannot call method “match” of null

Could you assist please?

martins.vanags · March 11, 2024, 6:00am

Hi,
See a similar use-case here:

In your case, issue.fields.resolutiondate must be used.

Martins / eazyBI

Leandimoll90 · March 13, 2024, 12:41pm

Hi Martins, I have updated the field as below but receive an error:
[jira.customfield_hodc]
name = “Hour of Day Created”
data_type = “integer”
dimension = true
javascript_code = ‘’’
issue.fields.customfield_hodc = parseInt(issue.fields.resolutiondate.match(/T(\d\d):/)[1]);
‘’’

martins.vanags · March 13, 2024, 1:04pm

Try this definition instead:

[jira.customfield_horesd]
name = "Hour of Resolution"
data_type = "integer"
dimension = true
javascript_code = '''
if(issue.fields.resolutiondate){
issue.fields.customfield_horesd = parseInt(issue.fields.resolutiondate.match(/T(\d\d):/)[1]);
}
'''

Martins / eazyBI

Leandimoll90 · March 13, 2024, 2:27pm

martins.vanags:

Sum(
  Filter(
    Except([Created hour].[Created hour].Members,
    [Created hour].[(none)]), 
  Val([Created hour].CurrentHierarchyMember.Name) < 9 OR 
  Val([Created hour].CurrentHierarchyMember.Name) >= 18
), [Measures].[Issues created])

This is working now! Thank you so much!!

pbenati · August 28, 2024, 1:11pm

Martins – What time zone is this report referring to? Thanks.

pbenati · August 28, 2024, 1:39pm

Martins – How are you color-coding the cells of the report? Thanks.