Right trim on numeric

Hi,

In the below table the first column has data type fixed decimal 19.4. Now in the second column i want to have the output as in below example

109.1574 --> 109.15

So what i tried is tried to convert the value as string limiting to length "6" . i need to use column in some numerical calculation so when i change the data type back to Fixed decimal 19,2 .. the values are rounding off.

Another example 103.1051 ... what i want to use in formula is 103.10 NOT 103.11 as shown in the below image..

any idea on how to retain the values i want but still be in numeric ?

Datasets

Preparation

Accepted answers

jdunkerley79

I'd suggest:

FLOOR([Value]*100)/100

I did a blog on various roundings a while ago: https://jdunkerley.co.uk/2015/07/29/rounding-calculations-in-tableau-and-excel-and-alteryx/

All comments

BenMoss

You can use TOSTRING([field]) to allow you to perform string type functions, and tonumber to convert it back.

For example...

TONUMBER(LEFT(TOSTRING([FIELD]),2))

jdunkerley79

I'd suggest:

FLOOR([Value]*100)/100

I did a blog on various roundings a while ago: https://jdunkerley.co.uk/2015/07/29/rounding-calculations-in-tableau-and-excel-and-alteryx/

vrbravo

I dont think would give result as needed because the Left function would give only the left two digits i.e 10 . So i tried then Tonumber(Substring(Tostring(xxx),0,5)) kept the data type as 19.2

vrbravo

This is what i was expecting. Thanks !!

so i tried the following and brought me the same .! little bit unwanted method

if [Value] < 100 then

Tonumber( Substring(Tostring([Value]),0,5))

else Tonumber(Substring(toString([Value]),0,6))
endif

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335