Count issues created at certain period of hours of the day

Questions & Answers
1

Hello there!

Please, help.
I have to create a report a report showing the issues created at a certain period of the day.
Something like:

Issues created at: morning (6am to noon): 22 issues
Issues created at: afternoon (noon to 6pm): 31 issues
issues created at nigh (6pm to midnight): 7 issues
Issues created at dawn (midnight to 6am): 3 issues.

The idea is to hava a table with this numbers so I can show it in colums.
I’ll use the dimension time to change de period (this month, previous month…)

Can anyone help me? I believe this is a easy task, but I’m new at EazyBI and english is not my native language.

Edit: I’m using Jira Server, 7.13. EazyBi 5.3.2

Thanks!

2

Hi @Yuri_Sant_Ana,

You can try to create four new calculated members that extract the hour component from the issue created date. Then you can compare it to the criteria and filter out the ones matching it. Have a look at an example below:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) >= 6
    AND
    Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) < 12
  ),[Measures].[Issues created]
)

See a picture of a sample report below:
Screen Shot 2020-05-18 at 11.00.10

The eazyBI documentation page has more information on creating calculated measures and members - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members.

Best,
Roberts // support@eazybi.com

3

Hi,
this works great. However, I have the same Problem. But I need to show times like
7:30 am - 11:30 am
changing the hour value to ‘7:30’ in the script formula does return a failure.
Could anyone advise how to do this correctly.
Many Thanks in advance!
Josef

4

Hi @Josef.Wagner,

There is a function to retrieve the minute portion of the datetime field, just like the function used in the original calculation. I recommend you combine the two. Please use the recommended function below:

Minute(DateParse([Issue].CurrentHierarchyMember.get('Created at')))

Try to adapt the approach to your use case. Let me know if I can assist with anything.

Best,
Roberts // support@eazybi.com

5

Hi Roberts,
how could I with the Minute(DateParse… create internvals / calculated measures, e.g. “morning 7:30 - 11:30” ; “noon 11:30 - 13:30” and finally “afternoon 13:30 - 17:30”
Any Idea?
Thanks and regards,
Josef

6

Hi @Josef.Wagner

I missed that the Tips&Tricks section of the community site has an example that I could have pointed you toward - Issues created out of office hours. See the last part of the first post.

But the calculated measure formula for the period 7:30-11:30 could look similar to the one below:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    (
      Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) > 7
      OR
      Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) = 7
      AND
      Minute(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) >= 30
    )
    AND
    (
      Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) < 11
      OR
      Hour(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) = 11
      AND
      Minute(DateParse([Issue].CurrentHierarchyMember.get('Created at'))) <= 30
    )
  ),[Measures].[Issues created]
)

Best,
Roberts // support@eazybi.com