Formula to Create New Column Based on Criteria

[adamscy]

I have a report that I created in Alteryx but need help creating a new column within the report to produce a new "Report Amount" column.  There are certain "Accounts" in the report that have amounts in the "Debit" column and others with amounts in the "Credit" column.  I would like to have a formula to create a new "Report Amount" column based off of some criteria.  I've put the information in the attached report but here is what I'm trying to do.

If the Account column = one of the below and there is an amount in Credit column then use Credit amount, otherwise put the negative Debit amount in the new "Report Amount" column. 

1.  HFM_CbCUnRelRevenue

2.  HFM_CbCRelRevenue

3.  HFM_CbC_EBIT 

4.  HFM_CbC_Capital

5.  HFM_CbC_RE

If the Account column = one of the below and there is an amount in Debit column then use Debit amount, otherwise put the negative Credit amount in the new "Report Amount" column. 

1.  HFM_CbC_TotalTax

2.  HFM_CBC_CurrentProvisionTotal

3.  HFM_CbC_TangAssets 

[Luke_C]

Hi @adamscy 

You can use an if statement to do this: 

[binuacs]

@adamscy One way of doing this

[adamscy]

Thanks Luke!  I tried the formula but it is not working, I've attached some results.  The accounts that should be positive 'Credit' are negative and none of the 'Debit' amounts are following to the new Report Column.

[Luke_C]

Hi @adamscy 

Ran the new data through and it looks fine. How did you implement the formula?

[adamscy]

I copied your formula directly into my workflow and I'm not getting the desired results for some reason.  The preview even shows the negative amount but it should be positive.

[binuacs]

@adamscy Another option writing the formula based on the CR/DB (Mappinig from the second tab)

[Luke_C]

Hi @adamscy 

Are you certain your account descriptions match? The only thing I can think of is that the final else clause is negative credits, which would cause the behavior you're seeing. Maybe modify to: 

if [Account] in ('HFM_CbCUnRelRevenue','HFM_CbCRelRevenue','HFM_CbC_EBIT','HFM_CbC_Capital','HFM_CbC_RE') and !isempty([Credit])
then [Credit]

elseif [Account] in ('HFM_CbCUnRelRevenue','HFM_CbCRelRevenue','HFM_CbC_EBIT','HFM_CbC_Capital','HFM_CbC_RE') and isempty([Credit])
then -[Debit]

elseif [Account] in ('HFM_CbC_TotalTax','HFM_CBC_CurrentProvisionTotal','HFM_CbC_TangAssets' ) and !isempty([Debit])
then [Debit]
elseif [Account] in ('HFM_CbC_TotalTax','HFM_CBC_CurrentProvisionTotal','HFM_CbC_TangAssets' ) and isempty([Debit])
then -[Credit]

else null()

endif

[adamscy]

Yes I have verified the account names are correct but none of the formulas are working.  I'm still trying to figure it out.

[binuacs]

@adamscy  Can you upload your new input file and mapping again?