Trunc or TruncNum

[Number4]

How would I perform this calculation from Excel, in Alteryx?

"=TRUNC(131.99 -3, -1 ) + 9.99"  which equals 129.99

TRUNC(131.99,-1) = 130.00

TRUNC(131.99-3,-1) = 120.00 (131.99-3 = 128.00)

[Number4 · Accepted solution]

Well, here's my solution.  Has to be something better.

                       [Field]

Floor( (Floor ([131.99] ) -3) / 10) * 10 + 9.99 = 129.99

[estherb47 · Accepted solution]

Hi @Number4 

If your solution works in this case, go ahead and mark yourself with a solution!!

I wanted to recreate the Trunc function so that you (and others) can use it more dynamically.

The input takes 2 columns. The original number, and the number of digits. This is exactly what you'd put inside of the =TRUNC function in Excel.

First, pull out the decimals, if any, into their own string (new field [DecimalString]). 

A Regex replace pulls all of the numbers to the right of the decimal place, whether it's a period or a comma:

REGEX_Replace(ToString([Number]),"(\d+?\.|\,)(\d+)" ,"$2")

Next, we'll count the number of decimals, in case the number of digits is a positive number. This may be a very convoluted way to get there, but it does the job. If the number doesn't contain a ".", then there are 0 decimals. Otherwise, count all of the digits that appear in the DecimalString field

If !Contains(ToString([Number]), ".") THEN 0
ELSE REGEX_CountMatches([DecimalString], "\d")
ENDIF

Next function will recreate TRUNC fully. If the number of digits is negative, then we need to truncate all of the decimals, and we need to truncate by 10s as defined by the "digits" field. The FLOOR function removes the decimals. Then we take away digits from the left side of the decimal (if digits is -1, then remove the 10s place by subtracting the rightmost number in the decimal-less number from the decimal-less number. If digits is -2, remove the 100s place, and so on):

IF [Digits]<0 THEN FLOOR([Number])-ToNumber(Right(tostring(FLOOR([Number])),-[Digits]))

If digits is positive, then we're only focused on removing decimals. If the number of decimals is equal to or greater than Digits, no need to adjust the number

ELSEIF [NumDec]<=[Digits] THEN [number]

Otherwise, chop off the decimals with the Floor function, and add only the decimals as defined by the digits. We calculate the decimals needed by taking the leftmost [Digit]s from the [DecimalString] (so if our decimalstring is 96, and our Digits is 1, that leaves us with 9 -- the first digit from the string), dividing it by 10 to the power of the number of [Digits], and adding that to the decimal-less number

ELSE FLOOR([Number])+tonumber(left([DecimalString],[Digits]))/pow(10,[Digits])
ENDIF

Fun challenge!!! Let me know if that helps. I'm attaching a sample workflow so you can see it in action. To add and subtract numbers, create a new field that you'll feed into all of the above functions.

Cheers!

Esther