Trouble showing Decimal Value - Still getting 0

[Daisy_Ramirez]

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
)

Fix for expression giving 0% or missing decimal part Tips & Tricks 💡

Trying to divide two numbers to get a percentage but always getting 0? Trying to divide two numbers but losing the numbers after the decimal point? TRY THIS Convert one or both of the values in your division expression to a Decimal value, either by: (1) wrapping the value in the expression with the DECIMAL() function; (2) multiplying the value in the expression by 1.0; or (3) changing the column’s type to Decimal if suitable to the app. FOR EXAMPLE If computing a percentage from two Number co…

[Steve · Accepted solution]

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:

help.appsheet.com

TOTALHOURS()

Count of hours in Duration

[Daisy_Ramirez]

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

[Daisy_Ramirez]

Hi Steve, no luck.

Changed the vir_work_hrs:

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
)
)

[Daisy_Ramirez]

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