Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
you need to provide real data and show your flow or at least a functional snippet. you can cut out or mask other columns to maintain data security, same with the flow.
As it is anyone trying to help is shooting in the dark.
here is my shot in the dark...
I took your 2 row data file and built a quick flow using your formula and I got a "1" on the first record where base amount was 30.19 and a "0" on the next. just as you say it should work. That said I suspect the prior suggestions that it is a rounding issue is indeed the case.
The fact that you are rounding the same way is not a guarantee. Alteryx uses a flavor of C under the cover and so rounding is from the value in the discarded position. If you have a value that is in truth 2.1749999999998 as a double and you round to 2 decimal you will get 2.17 because the dropped value is 4 so it rounds down without regard to the 9 in the 4th position. If you rounded to 3 decimal you get 2.175. if you round to 3 positions and then round that to 2 decimal you get 2.18 which one might argue is the right answer for an original value of 2.1749*.
Depending on how your values are derived or acquired you can have different "actual" working values being rounded. Strings converted for math are made double and not all values convert cleanly, i.e it may result in a working value that is more or less than expected by a fraction. Below are examples where the exact same values are used to calculate a result. The first shows a calculation that in double form results in 2.175...but you will see in the second illustration it is actually 2.1749999* when using fixed decimal with enough decimals specified. each illustrations shows several variations using different rounding to show the different effects.
The key is to understand it all hinges on the dropped position. It is important to understand how other systems round if you are trying to reconcile back to other systems. In my example the source billing system has 3 different calculations to derive the actual rate depending on why the rate is established, initial, or rate change for one of 2 other reasons, each having different number of decimals used in calculation. so knowing that and how Alteryx/C# rounds I can build the correct calculation.
In your case you probably have a conversion or calculation/rounding that is subtly different for one column resulting in the tiniest of differences in actual/working value. try converting your data types to fixed decimal with 10 to 14 decimals to force the result preview to show what is actually there, if you use double the preview section shows a rounded result which Alteryx warned when they added the result preview is not necessarily the true/actual value used in calculation.
Note also that working values within a formula and withing the same formula tool may differ from the final result passed downstream to other tools. You create a field with fixed 2 decimal but in the calculation it is converted by the system to double for the actual math which may be a 3 decimal more or less than you expect or see in the preview area...but once the field is passed downstream it is the rounded 2 decimal result you might have expected...can get messy.
I did a slide on this issue in a recent Tamp Fl User Group meeting you can probably find posted (Tips & tricks for Feb 2018 meeting)
