topic Re: Formula Tool - Subtract Fields Question in Alteryx Designer Desktop Discussions

Formula Tool - Subtract Fields Question

jfxh — Wed, 17 Apr 2019 03:29:22 GMT

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]

[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!

Re: Formula Tool - Subtract Fields Question

nerces — Wed, 17 Apr 2019 03:47:47 GMT

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

Re: Formula Tool - Subtract Fields Question

jfxh — Wed, 17 Apr 2019 03:49:29 GMT

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.

Re: Formula Tool - Subtract Fields Question

nerces — Wed, 17 Apr 2019 04:08:11 GMT

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.

Re: Formula Tool - Subtract Fields Question

jfxh — Wed, 17 Apr 2019 04:50:44 GMT

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?

Re: Formula Tool - Subtract Fields Question

nerces — Wed, 17 Apr 2019 05:24:04 GMT

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.

Re: Formula Tool - Subtract Fields Question

jfxh — Wed, 17 Apr 2019 05:52:26 GMT

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!