Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,300,356
Community Members
 
Community Events
165
Community Groups

Calculation of incorrect hours and minutes in eazyBi

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

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

Hello @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

Hi @Pooja Singh ,

 

For days you could use the same approach @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

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
Community showcase
Published in Apps & Integrations

Marketplace Partner Spotlight: AppLiger

This month the spotlight is on AppLiger. We caught up with Pavel Pavlovsky, CEO and Product Manager, to learn how the company started and what fuels the team's creativity.    Atlassian:...

397 views 0 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you