Trouble showing Decimal Value - Still getting 0

I’m having trouble displaying a decimal result from the formula below after using Steve’s Tip. I have all expressions wrapped in the 1.0 to indicate decimal but I’m not sure what I’m missing. What’s interesting is the correct value will flash on the screen quickly but then revert to 0)

vir_sum_whrs (datatype=Decimal):
IF(
SUM(SELECT(Receiving Work Hours[vir_work_hrs],[receiving_id]=[_THISROW].[receiving_id]))<0,
0,
(SUM(SELECT(Receiving Work Hours[vir_work_hrs],[receiving_id]=[_THISROW].[receiving_id])))*1.0
)


Your vir_sum_wkhrs expression, formatted to my preference:

IF(
  (
    SUM(
      SELECT(
        Receiving Work Hours[vir_work_hrs],
        ([receiving_id] = [_THISROW].[receiving_id])
      )
    )
    < 0
  ),
  0,
  (
    (
      SUM(
        SELECT(
          Receiving Work Hours[vir_work_hrs],
          ([receiving_id] = [_THISROW].[receiving_id])
        )
      )
    )
    * 1.0
  )
)

Another, more efficient way to do it:

MAX(
  LIST(
    0.0,
    SUM(
      SELECT(
        Receiving Work Hours[vir_work_hrs],
        ([receiving_id] = [_THISROW].[receiving_id])
      )
    )
  )
)

Expression for vir_work_hrs:

(((((HOUR([Work Hours]) * 60) + MINUTE([Work Hours])) / 60)) * 1.0)

HOUR(...) gives Number.

number * 60 gives Number.

MINUTE(...) gives Number.

number + number gives Number.

number / 60 gives Number. Fractional component is lost!

number * 1.0 gives Decimal, essentially by appending .0 to the Number value.

Try instead:

(((HOUR([Work Hours]) * 60) + MINUTE([Work Hours])) / 60.0)

I.e., divide by 60.0 (Decimal) rather than 60 (Number).

Or just:

TOTALHOURS([Work Hours])

See also:

1 Like

Thanks Steve! Will give it a go and report back!

Hi Steve, no luck.

Changed the vir_work_hrs:
image

Changed vir_sum_hrs to
IF(
(
SUM(
SELECT(
Receiving Work Hours[vir_work_hrs],
([receiving_id] = [_THISROW].[receiving_id])
)
)
< 0
),
0,
(
(
SUM(
SELECT(
Receiving Work Hours[vir_work_hrs],
([receiving_id] = [_THISROW].[receiving_id])
)
)
)
* 1.0
)
)
image

Sorry Steve, looks like we’re all set. System just didn’t save the change. Thanks so much!
image

2 Likes