LinkedIn
Twitter
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!
Solved
1 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.
