Why is my formula tool giving an incorrect result?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a lot of pairs of Debit and Credit columns like so:
I want to find the unusual and usual ones, with the condition that:
- if ANY the Dr >0 AND the Cr >0, then Usual, else Unusual.
So my formula tool is like this:
I don't think there's anything wrong with the logic in the Formula tool? But I don't understand why the results would all be unusual, when in the screenshot above, all of the first four rows should have been usual because Dr 76>0 and Cr 76>0
When I tried using a sample data using text input, it works fine:
Why is Formula tool giving incorrect results when I'm using my original data? What's happening?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@novalcia
Never believe Brutal force😂
Here is a dynamic approach and hope it will help get started.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@novalcia in your second screen shot
part of your formula is sum dr2 >0 and in the data tab it is 0, hence the condition is not met, you might need to update it to >=0
also check that the field data is in float/double format
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @novalcia - the solution from @Qiu here is definitely a better approach as it's fully dynamic. However, to answer the issue at hand, this stems from incorrect use of parentheses as - if you do stick to your current approach - you need to separate each 'OR' into it's own check. See my blog here, particularly the final section of 'My AND/OR statement isn't returning the correct values!':
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The second screenshot was the trial data I used, which works as expected.
The actual data I used is the first screenshot. The 76 and 77 pairs are supposed to be usual but it was categorized as unusual by alteryx.
The data are all in double format
