Bug - SUM() with decimals

[Alejandra_Petro]

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?

Thanks in advance for your help!

[Marc_Dillon · Accepted solution]

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.

[Alejandra_Petro]

I’ll make some adjustments. Thanks for your inputs

[Steve]

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

Or consider SWITCH() instead:

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

[Alejandra_Petro]

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