Calculation of incorrect hours and minutes in eazyBi

Luiz Ricardo Pereira da Silva December 18, 2017

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:

horas _duplicadas.PNG

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

1 answer

0 votes
Sir Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 28, 2017

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 February 17, 2019

Hello @Sir Mārtiņš Vanags ,

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 27, 2019

Hi @Pooja Singh ,

 

For days you could use the same approach @Sir Mārtiņš Vanags 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

Like Pooja Singh likes this
Abhinav Ojha
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 23, 2021

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 

Like Roberts Čāčus likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events