Weird result from formula

[mb1824]

Hello,

In the below screenshot, [Amount] & [Amount2] are both data type 'Double' (size 8)...

[Amount2] is the formula as shown.

Why aren't all the [Amount2] from Record 8 to Record 33 changed from 105882 to 105881.83?

[LordNeilLord]

Thats a bit odd!

As a test can you try if Floor(Amount) = 105882....

If that still deosn't work, can you attach your workflow?

[ThizViz]

Off the top of my head, I've run into some strange math behavior in Alteryx when I unknowingly had NULLs in my data. 

So the first thing I would do is use a formula tool on Amount that says

IIF(IsNull([Amount]),0,[Amount])

(**shout out to @MarqueeCrew as it's been years since I used that formula).

The other thing that occurred to me is that "Amount" really isn't a whole number even though it looks like one (and if there were decimals, they should be displayed), but @LordNeilLord's suggestion will handle that issue. 

[mb1824]

@LordNeilLord@ThizViz

thanks for the comments.

I tried Floor(Amount) and that didn't work. There are also no nulls in my data

I tried changing [Amount] from Double to Fixed Decimal and that does work.

Anyway, I have a attached workflow with data for one period.

The background (not included in workflow) is that I have used the Batch Input Macro to get 3 cells from 36 workbooks and then output that to a yxdb file. The attached is the same data, i just filtered the yxdb down to one period.

[brindhan · Accepted solution]

Hi @mb1824

The below formula gives the desired result on Double data type input:

IF round([Amount],1)=105882 THEN 105881.83 ELSE [Amount] ENDIF

The Alteryx input is stored as double with precision points, so the round function gets it to the required format before the computation

reference: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculations-with-Field-Type-Int64/td-...

"The problem is unfortunately the Alteryx formula tool supports a smaller set of datatypes than the full Alteryx set.

Namely: String, Double and Spatial

Everything gets converted to one of these three types as it goes into an Alteryx formula and then the result is converted back to the requested type after the formula has been calculated."

Hope this helps!

Cheers,

Brindha