Event Duration Field

Marc René shared this question 59 days ago
Answered

I have two questions. I created a report that displays Applications and duration of the downtime events for a certain month.

1. I would like to sum up all duration times for the application in totaal.

2. We have a field "Event Daration" its a sum of time in seconds. From this field i would like to create field that displays in hours:minutes:seconds

Comments (10)

photo
1

Hi Marc,

Thanks for reaching out. You may be able to accomplish this via Freehand SQL Calculated Fields. For example: How to convert Seconds to HH:MM:SS using T-SQL.

If you're using a different RDBMS than SQL Server, you should be able to search online for how to convert secionds to hh mm ss format and find other results. Also, in general, Support can try and give pointers and provide assistance where able, but report building and providing specific SQL queries is more in the realm of Consulting. Support typically handles things like how-to's, logs product enhancements, and addresses product defects and the like. Nevertheless, hopefully this helps!

Please let me know if you have any additional questions or concerns on this.

Regards,

Mike

photo
1

Thanks Mike,


This worked and i also found the "format"setting to convert it as well. So this solves my second question.

How can i sum up the totals when i have multiple entry's(events) per application.

The end goal is to make a availability % so 100% minus the time of the event duration.

I have the calculations of what makes "100%".. is it posible to put this calculation in a field?

photo
1

I made a calculated field with a SUM functionality that does part of the trick.

1. Now i have to create a field for each SLA time. Then is have the total available time and i can substract the time from the created calculted field with the SUM value.

2. And then i have to set that result to a %.

photo
1

Hi Marc,

Glad to hear you got the first part sorted! It sounds like all you need at this point is to convert your final numeric value to a percentage, correct? If you have two fields, one with the Total Time, and another with the created Calculated Field value, and you need to know what percentage against the total that is, you can then apply a Percentage Against Column or possibly a Percentage Against Maximum Advanced Function:

/8ea265bf7ad54e202d29270324366b6c

/9cf02f96326d10e5c31b48b9cc883e14

Please let me know if this appears to be what you're looking for here.

Regards,

Mike

photo
1

Thanks for this. Starting to like yellow fin more and more for its simplicity in thinking.


How do i create field with static data? I need to create a field with a sum of hours in a month.

we have two flavours.

1. a sum of 8 Business hours between 07:00 and 18:00 for five days a week

2. a sum of al hours in the current month


thanks in advanced

photo
1

Hi Marc,

Thanks! I hope we covered most of the standard use cases by now, ha!

To accomplish this leads me back to the same method as your original question - via Freehand SQL Calculated Fields. You'll probably want to use a function like DATEDIFF(), but you can search more online for potential SQL statements you can utilize to accomplish this. Here's one example: How to calculate difference in hours (decimal) between two dates in SQL Server?

Hopefully this helps! Please let me know if you have any further questions.

Regards,

Mike

photo
1

Hi Marc,

I just wanted to check in and see how things are going with this. Do you need anything else from us?

Regards,

Mike

photo
1

Hi Marc,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

photo
1

Good Day Mike,

Thanks again, you helped me out perfect. My questions are awnserd enough. I can go forward.


Thanks again Mike.

photo
1

Hi Marc,

You're welcome, and thanks for confirming! Please don't hesitate to reach out with anything else.

Regards,

Mike