Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

In-DB formula division - rounding down to nearest whole number

oracleoftemple
9 - Comet

Here's my situation.  I'm trying to use an In-DB Formula tool to divide two Int32 whole numbers.  I specify that the output should be Double so fractions will be displayed if applicable.  My formula looks like this:  ("Retail" / "Actual").  The problem is it's not including the remainder, i.e., it's rounding down to the nearest whole number.  So 4 / 5 gives me 0, when I want it to return 0.8.  8 / 5 gives me 1, when it should give me 1.6.  NOTE:  it's not rounding to the nearest whole number, otherwise 4 / 5 would have given me 1 and 8 / 5 would have given me 2.  It's just knocking off the fractional piece and rounding down.  Does anyone know how to fix this?  I know how to do it with a regular Formula tool, but the In-DB tool is SQL and I'm not quite sure what it is that I need to do differently.

1 REPLY 1
oracleoftemple
9 - Comet

Got it!

 

cast("Retail" as float) / cast("Actual" as float)

Labels