Issues created out of office hours

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([Created hour].[Created hour].Members,
    [Created hour].[(none)]), 
  Val([Created hour].CurrentHierarchyMember.Name) < 9 OR 
  Val([Created hour].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!

4 Likes

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

Hi,

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

Martins / eazyBI support

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

@Izabella_Cikalova

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

Martins / eazyBI support

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

@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

Thank you @martins.vanags for you help.

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]);
'''

@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

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!

@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

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

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?

Hi,
See a similar use-case here:

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

Martins / eazyBI

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]);
‘’’
image

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

This is working now! Thank you so much!!