Custom Column Formula does not yield desired results

  • 1
  • Question
  • Updated 3 years ago
I'm attempting to build a report that will give me the total number of hours a resource has, minus the PTO hours that they have booked. I used the following: ActualAvailableHours - ActualTimeOffTakenHours However, this only gives me a result if they have taken hours off. If they have not taken any time off, the result is blank. Is this due to the second value being null? Is there a way to validate it's a number before subtracting it?

(Originally posted by roguen.keller)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes

Posted 3 years ago

  • 1
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
Hi Roguen,

Thank you for using Replicon Community.

Yes, this is due to the Timeoff taken Hours (Selected Dates) being zero. To validate this, kindly enable this column in same report. A proper date range must be selected and not set to Entire Project.  I have demonstrated this in the attached screenshot (Monthly Resource Utilization_1.png). 



Thanks,





 

(Originally posted by Lingaraj Dharwad)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
HI Lingaraj, Yes, I understand that this is the way it's currently working, and it is due to the Timeoff column being empty. What I'm asking is if there is a way to get a numerical value in my custom column even when the Timeoff value is empty.

(Originally posted by roguen.keller)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
Hi Roguen,

Kindly create a new custom column with the below formula: 

IF(IsNull(ActualTimeOffTakenHours)=1,ActualAvailableHours,ActualAvailableHours - ActualTimeOffTakenHours)

This will basically display ActualAvailableHours if ActualTimeOffTakenHours is zero. 



Thanks,

Lingaraj



 

 

(Originally posted by Lingaraj Dharwad)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
This was successful. Thanks for your help!

(Originally posted by roguen.keller)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
helpful. Is there a similar workaround to be able to handle negative numbers in reports. Custom numeric fields are ignoring negative numbers in reports. Support strangely said "in Gen 3, negative numbers are no longer supported for numeric fields.

(Originally posted by sansari)
Photo of Archived Post

Archived Post, Official Rep

  • 2132 Posts
  • 5 Reply Likes
Hi,

Thank you for using Replicon Community.

Currently, I do not have a work around, however, can confirm that this issue has been escalated to the development team for further resolution. Once resolved you should be able to view negative values in reports. One of our Product Support Specialists will get in touch with you from time to time to provide regular updates. 



Thanks,

Lingaraj



 

(Originally posted by Lingaraj Dharwad)