# Bug - SUM() with decimals

Hi Community,

I have a virtual column [Validation] that returns “Compliant” if the SUM() of the values [Steer] , [Drive], [Tri-Axle1] and [Tri-Axle2] is equal to the [Gross] and the [Gross] is less or equal to the [Gross_Limits]; and returns “Non-Compliant” if the conditions are false.

Below is the expression:

IF(
AND( SUM(
IFS(
([Combinations] = “BDH”), (LIST([Steer],[Drive],[Tri-Axle1],[Tri-Axle2])),
([Combinations] = “WFC”), (LIST([Steer],[Drive],[Tri-Axle1]))
)
) = [Gross],
[Gross] <= [Gross_Limits]
),
“Compliant”,
“Non-Compliant”
)

The data type for all the columns is Decimal except the VC. (See screenshot)

The problem I came across is with the following case:

The VC returns “Non-Compliant” when I use a value with zero (0) as the first decimal digit.

[Gross] = 43.60
[Gross_Limits] = 44

The VC returns “Compliant” when I round the value to 6.1

The sum of the values is 43.60 for both cases, which equals the [Gross], and it is less than 44, which is the value of [Gross_Limits], so all conditions are true. Is there something I am missing?

Strange. I took the time to setup a similar situation in my testing app, and can confirm the behavior that you’ve shown.

Here are the columns:

formula for [sum] :

``````SUM( LIST( [dec1] , [dec2] , [dec3] , [dec4] ) )
``````

formula for [sum_equality] :

``````SUM( LIST( [dec1] , [dec2] , [dec3] , [dec4] ) )
= [gross]
``````

results:

If I set [sum_equality] to just:

``````[sum] = [gross]
``````

then it correctly returns a TRUE value.

If I change the formula for [sum_equality] to:

``````( [dec1]+[dec2]+[dec3]+[dec4] )
= [gross]
``````

then it correctly returns TRUE.

So it seems to be an issue with using SUM( LIST(…) ), inside a larger expression, instead of first saving the value as the required decimal type.

6 Likes

Your IFS() expression should have a default result:

``````IF(
AND(
(
[Gross]
= SUM(
IFS(
([Combinations] = “BDH”),
LIST([Steer], [Drive], [Tri-Axle1], [Tri-Axle2]),
([Combinations] = “WFC”),
LIST([Steer], [Drive], [Tri-Axle1]),
TRUE,
LIST(0.0)
)
)
),
([Gross] <= [Gross_Limits])
),
“Compliant”,
“Non-Compliant”
)
``````

``````IF(
AND(
(
[Gross]
= SUM(
SWITCH(
[Combinations],
“BDH”,
LIST([Steer], [Drive], [Tri-Axle1], [Tri-Axle2]),
“WFC”,
LIST([Steer], [Drive], [Tri-Axle1]),
LIST(0.0)
)
)
),
([Gross] <= [Gross_Limits])
),
“Compliant”,
“Non-Compliant”
)``````
4 Likes

Hi Steve, I tried both options, but the problem persists . However, I made some changes based on @Marc_Dillon notes, and it worked :). Below is the expression with the adjustments:

IF(
(AND(
(
IFS(
([Combinations] = “BDH”), ([Steer]+[Drive]+[Tri-Axle1]+[Tri-Axle2]),
([Combinations] = “WFC”), ([Steer]+[Drive]+[Tri-Axle1])
) = [Gross]
),
([Gross] <= [Gross_Limits])
)
),
“Compliant”,
“Non-Compliant”
)

Thanks everyone

2 Likes