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
)
Solved! Go to 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:
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:
Thanks Steve! Will give it a go and report back!
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
)
)
Sorry Steve, looks like weโre all set. System just didnโt save the change. Thanks so much!
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |