Calculation of incorrect hours and minutes in eazyBi

[Luiz Ricardo Pereira da Silva]

I need to create a custom field by calculating estimated hours, hours remaining and hours spent in a report, but setting ## h ## m, the hour and minute values end up repeating as in the image below:

How can I resolve for my report to bring estimated hours, hours remaining and hours spent according to the filter used?

[Martins Vanags -eazyBI-]

Hi,

The formatting you have selected (## h ## m) requires that value is converted to minutes in order to display results in hours and minutes.

I believe you could create new calculated members in "Measures" dimension and then you can multiply hour-related measures with 60 the to get result in minutes.

Kind regards,

Martins / eazyBI support

[Pooja Singh]

Hello @Martins Vanags -eazyBI- ,

Greetings!

Is it possible to convert those converted values to number of days (#h #m to number of days)?

Eg: if converted value is 72h, then number of days should be 9 (Considering that the number of hours per day = 8 hours)

Regards,

Pooja

[Roberts Čāčus]

Hi @Pooja Singh ,

For days you could use the same approach @Martins Vanags -eazyBI- suggested - multiplying the hours by 60 and select the 'Days Hours Minutes' formatting.

To display it in the workday fashion - 1 day = 8 hours, try to create a calculated measure with the formula below:

(Case 
When [Measures].[Hours spent]>8
Then Format(Int([Measures].[Hours spent]/8)
,"0d ")
Else ""
End)

||

(Case 
When [Measures].[Hours spent]>=1
Then Format([Measures].[Hours spent] 
- Int([Measures].[Hours spent]/8) * 8
,"0h ")
Else ""
End)

||

(Case 
When [Measures].[Hours spent]>0
Then Format(
(([Measures].[Hours spent] -
Int([Measures].[Hours spent]/8) * 8) -
Int([Measures].[Hours spent] -
Int([Measures].[Hours spent]/8) * 8))*60
,"00m")
Else ""
End)

Kind regards,

Roberts / eazyBI support

[Abhinav Ojha]

Hi @Roberts Čāčus ,

Thanks for the code, as it was a really big help. I was looking for this for many weeks. I would like to suggest a small improvement in the 2nd Case, where you are calculating hours. In my reports, it was wrongly calculating hours, especially when my total hours were a decimal number like 100.67. Days and Minutes were correctly calculated. Here is my suggestion, hope it is correct :

(Case 
When [Measures].[Hours spent]>=1
Then Format(Int([Measures].[Hours spent] 
- Int([Measures].[Hours spent]/8) * 8)
,"0h ")
Else ""
End)

Only thing I have changed here, is converting the whole calculation to "Int", so the number after decimal from this stage is not rounded off as hours, and instead gets calculated in minutes.

Best Regards,

Abhinav