Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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 Marketplace Apps & Integrations

🤺 Atlympic Event: Partnerships

Hello Community!  Quick disclaimer: We are running a contest on Community (The Atlympics!) from July 23rd - August 8th of 2021. If you are interested in participating in this contest (prizes! ...

112 views 0 6
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