Multiple IFS Statements

[quims]

Hi Alteryx Community,

I'm Quims and new to Alteryx, i would like to seek an advise on how to place IFs correctly in a formula function.

My Statements are:

If [Revenue] is equal or less than 0 then [Allocation] will be same as [Revenue]

If [Expense] is equal or less than 0 then [Allocation] will be same as [Expense]

If [Revenue] [Expense] is greater than 0 then [Allocation] will calculate based on formula i stated

Default formula:

([Total Revenue]*[Expense])/[Total Expense]

SN# 1 and 2 is formula by default but when applying it to SN# 3, 4 and 5, shouldn't 0 in Allocation column. I want it to reflect in Allocation column the number from Expense and Revenue if i got 0 results whether in Total Revenue or Total Expense Columns.

Thank you in advance.

[BenMoss · Accepted solution]

Hi!

You need to use a formula tool, with the fieldname set as 'Allocation'

You can then use the function below as the formula...

If [Revenue] <= 0 then [Revenue]

ElseIf [Expense] <= 0 then [Expense]

ELSE ([Total Revenue]*[Expense])/[Total Expense]

ENDIF

Ben

[Jonathan-Sherman]

Hi @quims,

Your formula should look along the lines of:

IF [Revenue] <= 0 THEN [Revenue]

ELSEIF [Expense] <= 0 THEN [Expense]

ELSE ([Total Revenue]*[Expense])/[Total Expense]

ENDIF

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[quims]

@BenMoss,

Thank you for the help! it works!

[quims]

@Jonathan-Sherman 

Possible with the number showing only between 0.00 to 0.09?

quims

[Jonathan-Sherman]

A little confused on the question you're asking there @quims?

[quims]

@Jonathan-Sherman 

Sorry for the confusion.

What i meant is the range or amount I'm looking is only between 0.00 to 0.09 (Debit) because I noticed if i will use <=0, the amount will be included the credit amount (-sign).