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

Formula Tool - Subtract Fields Question

jfxh
5 - Atom

Hi! Just a quick question, it seems exceedingly basic but I cannot find anything from searching the community.

 

Basically, I have a formula tool that creates a new Column. If the relevant fields of the incoming data [Sum_Cash Amt], [Sum_Revenue Amt], and [Sum_GST_IN Amt] is true in the following fashion:

 

-[Sum_Cash Amt] = [Sum_Revenue Amt] + [Sum_GST_IN Amt]

 

OR

 

[Sum_Cash Amt] + [Sum_Revenue Amt] + [Sum_GST_IN Amt] = 0

 

Then for that row, I want it to return "Risk Criteria 1"

 

However, I cannot get either to work (only need one of the above, so no need for an actual OR formula, methinks). Even when I export into excel and add up the fields, they equal zero. However, the formula returns a null, as per the formula below:

 

If -[Sum_Cash Amt] = [Sum_Revenue Amt] + [Sum_GST_IN Amt] Then "Risk Criteria 1" Else null() EndIf

 

I would not expect this to be the case, and hence am confused why it is not returning "Risk Criteria 1" for most of the rows. For an example, see Row 13 of the formula tool's output - although the figures add together to be nil/equal, "Risk Criteria 1" has not been returned.

 

Thanks!

 

6 REPLIES 6
nerces
8 - Asteroid

Can you please post a copy of the Excel sheet. Or at least a sample of it.

jfxh
5 - Atom

Hi Nerces,

 

Here you go - sorry about that.

 

The excel sheet itself is from another work flow, which is a lot larger. Let me know if you need that too.

nerces
8 - Asteroid

The issue is to do with your datatypes and rounding. Here is a solution. It's not the best solution but it should guide you to recognising where you went wrong.

jfxh
5 - Atom

Hi Nerces,

 

Thanks for you help - it works great.

 

Out of curiosity, when I tried to use the select tool to convert all the fields into Fixed Decimal with 19 scale and 2 precision, and then kept my original formula it did not work like your solution did (for example, for those where the total was a small non-zero value i.e. 0.00000000001 it did not return "Risk Criteria 1"). 

 

By the sound of it, I cannot convert my fields to Fixed Decimals first, instead I need to calculate with fixed decimals as my output format and then use the formula to assess whether it is nil or not. Is this correct?

 

 

nerces
8 - Asteroid

Yeh, it's a bit weird. Usually you have to convert it all to however many decimal points you want and then the output needs to be in the same format otherwise you get really small value in scientific notation that don't technically equal 0. You can always round those values to 0 decimal places to get an integer 0 or to 2 decimal places to get 0.00. I do apologise if some of this doesn't make sense or confuses you as it's not the easiest thing to explain over text.

 

 

Edit: I've attached a cleaner version of my workflow to this comment.

jfxh
5 - Atom

Thanks for that - I actually didn't notice you had changed the format on the multi-formula tool as well to make it fixed decimal. Will keep in mind in the future. Cheers!

Labels