cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

###### #SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

## IF AND statements

Atom

I am trying to create an if and statement based on where a certain number falls within a tier system. For example, if the "revised fee" amount is greater than the Fee 1 amount, the Fee 1 amount is multiplied by the Fee 1 % and the revised fee amount over the Fee 1 amount is multiplied by the Fee 2 %. I'm using the formula below and it keeps telling me "an expression cannot end with an operator. Does anyone know how to correct my formula?

IF [Revised Fee]<=0 THEN "0" ELSEIF [Revised Fee]=>[FEE 1] THEN [Revised Fee]*[FEE 1 %] AND [Revised Fee]>[FEE 1],[Revised Fee]<= [FEE 2],[FEE 1]*[FEE 1 %]+([Revised Fee]-[FEE 1])*[FEE 2%],[FEE 1]*[FEE 1 %]+([FEE 2]-[FEE 1])*[FEE 2%]+([Revised Fee]-[FEE 2])*[FEE 3 %] ENDIF

Alteryx Certified Partner

Hi @avajda that errors refers to this section of your if [Revised Fee]=>[FEE 1]  it should be [Revised Fee]>=[FEE 1]. Once you resolve this it returns a different error of malformed If function. I'm assuming the commas in your IF statement are further arguments which need to be stated in your IF. Also the first part of your If statement will create a string field not a numeric field.

I had an attempt of your If formula let me know what you think?

IF [Revised Fee]<=0 THEN 0 ELSEIF [Revised Fee]>=[FEE 1] THEN [Revised Fee]*[FEE 1 %] AND [Revised Fee]>[FEE 1] Elseif [Revised Fee]<= [FEE 2] Then [FEE 1]*[FEE 1 %]+([Revised Fee]-[FEE 1])*[FEE 2%] ELSE [FEE 1]*[FEE 1 %]+([FEE 2]-[FEE 1])*[FEE 2%]+([Revised Fee]-[FEE 2])*[FEE 3 %] ENDIF

Atom

Thanks for the feedback! However, I do need the first part of the formula to state if revised fee is < Fee 1 (and greater than zero), then Revised Fee * Fee 1%. Anything between 0 and the Fee 1 amount will be subject to the Fee 1%.

I did try your new formula and now it's giving me the following new error: Parse Error at char(0): Formula: invalid type in subtraction operator (Expression #1)

Any ideas?

Alteryx Certified Partner

Hi @avajda that is likely because your fields are numeric field and you are trying to equate them with "strings". It would be useful if you were able to share an example of your dataset to aid with your IF statement.

Bolide

if you format your formula for ease of viewing the issue becomes quite clear

IF [Revised Fee]<=0 THEN "0" ELSEIF [Revised Fee]=>[FEE 1] THEN [Revised Fee]*[FEE 1 %] AND [Revised Fee]>[FEE 1],[Revised Fee]<= [FEE 2],[FEE 1]*[FEE 1 %]+([Revised Fee]-[FEE 1])*[FEE 2%],[FEE 1]*[FEE 1 %]+([FEE 2]-[FEE 1])*[FEE 2%]+([Revised Fee]-[FEE 2])*[FEE 3 %] ENDIF

IF          [Revised Fee]    <=0              THEN "0"

ELSEIF [Revised Fee]    => [FEE 1]   THEN [Revised Fee]*[FEE 1 %]    AND   [Revised Fee] > [FEE 1] , [Revised Fee] <= [FEE 2] , [FEE 1]*[FEE 1 %] + ([Revised Fee]-[FEE 1])*[FEE 2%] , [FEE 1]*[FEE 1 %]+([FEE 2]-[FEE 1])*[FEE 2%]+([Revised Fee]-[FEE 2])*[FEE 3 %]

ENDIF

Your ELSEIF has a THEN but that is followed by an AND and then multiple classes separated by comma...seems like you are writing in another language at this point...

the basic form is

IF        condition THEN action

ELSE  action

ENDIF

the extended version

IF           condition THEN action

ELSEIF condition THEN action

ELSE  action

END

Labels