Forum members,
I am having trouble with a nested IIF Statement when trying to select the minimum of three values.
I have three possible values that I am trying to pick a buy target (BTTHREE) from. I have an "AvailToSpend" column, a "NeedToSpend column, and a "5PctFxd" column.
I need an expression in another column to pick the minimum value from the three possible values.
This expression is the closest I have come.
BTTHREE: IIf([AvailToSpend]<=[NeedToSpend],IIf([AvailToSpend]<[5PctFxd],[AvailToSpend],[5PctFxd]),[5PctFxd])
I have 96 rows returned by the particular query. The expression above returns the minimum value of the three in every case EXCEPT WHERE the "NeedToSpend" column is LESS THAN OR EQUAL TO the "AvailToSpend" column, in which case the expression returns the "5PctFxd" value. This occurs in 4 of the 96 records returned by the query.
It seems like I have tried a million different ways to get this right and just can't seem to do it.
Any help on that expression or an easier way to return the minimum of three values will be greatly appreciated. Thanks in advance for any help.
Glenn