Time calculation from JIRA but not date/days

Questions & Answers
, , ,
1

I have JIRA ticket format of calculating time for the activity on different days. Activity is planned for three days and on same time everyday(3:30 Pm to 5:30 PM) everyday, which means 2 hrs each day and in total 6 hrs.
Below fields in my JIRA ticket where we mention date and time
Change Start Date: 21/Aug/23 3:30 PM
Change End Date: 23/Aug/23 5:30 PM

If I am able to get the time difference in a day then I could able to get consolidated hrs spent on activity by myself, or fine if anyone able to share script for above ask.

2

Hi,

I don’t quite understand what you want to implement at this time. But maybe this will help you find a resolution for you:

NonZero( – Returns blank, if Result = 0
Count( – Sum of all Issues
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod( – Check if Created Date in Time Period
[Issue].CurrentMember.Get(‘Created at’),
[Time].CurrentHierarchyMember
)
AND – Filter Time
((Hour(DateParse([Issue].CurrentMember.Get(‘Created at’))) >= 15
AND
Minute(DateParse([Issue].CurrentMember.Get(‘Created at’))) >= 30
AND
(Hour(DateParse([Issue].CurrentMember.Get(‘Created at’))) <= 17
AND
Minute(DateParse([Issue].CurrentMember.Get(‘Created at’))) <= 30)
))
)
)
)

This code counts all tickets created between 15:30 and 17:30.

3

Hi @Luke ,

Thanks for responding to my query,
I believe above script is to just count the tickets that are created between the time but my ask is

Consider one ticket(ABC-1234) which is under the Category of “Daily Same time” with below activity start & End date and time.
Change Start Date: 21/Aug/23 3:30 PM
Change End Date: 23/Aug/23 5:30 PM

Concept of above ticket is to perform activity/outage hours on 21st Aug from 3:30 PM to 5:30 PM(2 hours outage) , 22nd Aug from 3:30 PM to 5:30 PM(2 hours outage ) and then again on 23rd Aug from 3:30 PM to 5:30 PM(2 hours outage). So in total outage hours calculation should be 6 hours but I only able to calculate continues outages from 21st Aug 3:30 PM to 23rd Aug 5:30 PM(21 hours excluding non working hours).

So I need help on calculating just impacted hours(6 hours). Let me know if any further clarification needed.

4

Can above concept be write in Javascript ?
Because I doubt if custom field has time along with date then I am not sure how we could just split time and calculate.
We can split via Javascript but someone has to help how we can infuse Javascript in MDX.

5

Hi @kalaiarasan18,

I think you may want to consider a different approach. If you enable time tracking, you can simply capture that a ticket took 2 hours (or whatever) to complete. If you can then easily identify this type of activity, which it sounds like you can, then you can simply sum the effort for any tickets within a date range regardless of what time they actually occurred. If the specific time is important to you, you may want to consider two custom fields to specify the support start and finish date/times for a given day. This will allow you to create the ticket independently of when it needs to take effect.

6

Hi @JoeC , That’s the real problem here, I was about to follow the same when I am able to calculate number of days with respect to that category(Daily Same Time) to multiply with hours spent in a day but not able to split the time for the day. Quite impossible to have another custom field to just to have time specific entry for this(since more reports may be missing if we try to pull oldest). And we cannot specify the time mentioned in a ticket in MDX because we have to pull the report for thousands of tickets on same logic.
There is a possibilities to split the time from custom filed via Java & Python Script(if we split with “T” from Data field) but not sure about MDX.

7

Hi, @kalaiarasan18

Welcom to the eazyBI community.

This is an exciting case; my approach would be, to first get the count of days and then multiply it by the duration - hours.

Please import the Change Start Date and Change End Date to eazyBI as a measure.

Then perform the calculation using DateDiffDays or DayDiffHours, to get the duration. The formula should look something like this:

(1+DateDiffDays(
  DateWithoutTime([Measures].[Start date]),
  DateWithoutTime([Measures].[End date])
  )
 ) 
*(
DateDiffHours(
  DateWithoutTime([Measures].[End date]),
  [Measures].[End date]
  
)
-
DateDiffHours(
  DateWithoutTime([Measures].[Start date]),
  [Measures].[Start date]
  
)
)

In the example, I’m using “Start date” and “End date”; replace it with your custom dates.

Let me know, how it went.

Kindly,
Ilze support@eazybi.com

8

Hello @ilze.mezite , Hooo
Hooo
 :slight_smile: :smiley:

It’s fantastically working as expected
thanks for your script and that made my day :slight_smile:

Thank you very much @ilze.mezite

1 Like