Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multiple IFS Statements

quims
8 - Asteroid

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]

 

quims_0-1580387312962.png

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.

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora

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
8 - Asteroid

@BenMoss,

 

Thank you for the help! it works!

quims
8 - Asteroid

@Jonathan-Sherman 

 

Possible with the number showing only between 0.00 to 0.09?

 

quims

Jonathan-Sherman
15 - Aurora
15 - Aurora

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

quims
8 - Asteroid

@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).

Labels