community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Weird result from formula

Asteroid

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?

 

Alteryx Formula.JPGAlteryx Formula2.JPG

Alteryx Certified Partner

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?

ACE Emeritus
ACE Emeritus

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. 

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Highlighted
Asteroid

@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.

Asteroid

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

 

 

 

 

Labels