Dynamic decimal places

[gian]

Hi all,
I would like to have my Conversion % column dynamically change decimal places according to the values it represents. The AUTO feature is not consistent, it does not automatically determine the appropriate number of decimal places.
As an example if the conversion values are small, I’d like to see decimals (i.e. 3.2%). On the other hand, if values are big enough decimals become irrelevant (i.e. 40%)
How can I achieve that? a formula maybe?

Thanks.
Gian Marco

[wstevens01]

Hi @gian ,

I created a calculated field as follows:

ifelse({decimal_num} < 100.0, round({decimal_num}, 2), {decimal_num} > 100.0 and {decimal_num} < 1000.0, round({decimal_num}, 1), round({decimal_num}) )

Using this in a table, it looks like this:

Is that what you were thinking?

ws

[gian]

Thanks @wstevens01 . I was trying to replicate that with percentages, but I cannot make it work.
My actual use case is:
IF Conversion% is less than 10% then return Conversion% with one decimal (example: 8.2%)
ELSE return Conversion% without any decimal (example: 14%).

Weird, but it does not work.

[wstevens01]

Hi @gian ,

Can you share your calculated field? And the results of the display with a couple values?

I’ll troubleshoot it with you.

ws

[gian]

Calculated field Dynamic CVR: ifelse(CVR >= 0.1, round(CVR), round(CVR, 1))
Results:

@wstevens01

[wstevens01]

Hey @gian ,

It looks like we’re wrestling with both precision and the behavior of round().

With percentage decimals, less than 1/greater than 0, you need that extra precision to get the correct round() behavior, but that extra precision is the problem that you’re trying to solve for.

The best I was able to do was to show that if you’ll get reasonable behavior if you pre-calculate the percent value (e.g. multiple by 100):

Here are the two calculated fields:
crv_dynamic:
ifelse(CRV >= 0.1, round(CRV, 0), round(CRV, 1) )
crv_pct_dynamic:
ifelse({CRV_pct} >= 10.0, round({CRV_pct}, 0), round({CRV_pct}, 1) )

This does lead to a display problem, because if you display the number as a percentage, you’ll get the wrong value. But the actual numbers are correct.

I’m guessing that you’ll end up showing a decimal or two for percentages over 10.

Let me know what you decide.

ws

[gian]

Hi @wstevens01,

Thanks so much again for trying to find a solution with me.
Unfortunately, it does not seem to work with me. Not sure what I might be doing wrong, if anything.

[ErikG]

Hi @gian

do you have the decimal places ono “Auto” for the dyn field?

BR

[wstevens01]

Good catch, @ErikG !

@gian , that’s the only way I reproduce your latest results.

Auto Decimal Places:

Custom Decimal Places:

Let us know if Auto fixes the problem

ws

[gian]

Hey @wstevens01 , @ErikG ,
Thanks for that, selecting Auto fixed that!

One thing is left, there no ‘%’ next to the numbers now though, which visually speaking, I think i critical for the end-user.

I tried this:
CVR dyn. % =

ifelse(
    isNotNull(CVR),
        concat(toString({CVR dyn. dec.}),'%'),
        NULL
)

But I get this:

Formatting does not include Decimal Place selection, as the CVR dyn. % is a string now.

Do you guys have a smarter way to do this too?

[ErikG]

@gian you could edit the toString before concat.

Get the position of the decimal=locate(toString({CVR dyn. dec.}),“.”)

If you don’t want decimal substr(toString({CVR dyn. dec.}),1,decimalposition-1)

You know what I mean?

[wstevens01]

@gian ,

This is what I meant about the display problem. To concat the % sign, you have to cast the number to a string and that automatically adds the decimal.

Eriks idea about looking for the decimal in the string is what I was thinking as well, but you’ll still have to determine which numbers to remove the decimal and which numbers to leave the decimal.

I’ll noodle more on it the morning, but I think that you’ll have to add the string manipulation to the ifelse statement.

ws

[wstevens01]

Hi @gian ,

This is start:

ifelse({CRV_pct} >= 10, 
    concat( split(toString(round({CRV_pct}, 0)), ".", 1), "%"), 
    concat( substring(toString(round({CRV_pct}, 1)), 1, 3 ), "%")
)

With the resulting display:

Notice that the substring() arguments are hardcoded to pick the first 3 characters. I think that’s OK because for a percent less than 10, it will always be a single digit number. If that’s true, than this should work for you.

Also note that as a string, you can’t do any numeric processing, like a sum() or max().

Let us know if that works for you.

Thanks to @ErikG for the string function ideas!

ws

[wstevens01]

@gian ,

I realized that using the string functions to manage the decimal, you don’t need the round() function. This calculation produces the same result as above:

ifelse({CRV_pct} >= 10, 
    concat( split(toString({CRV_pct}), ".", 1), "%"), 
    concat( substring(toString({CRV_pct}), 1, 3 ), "%")
)

Hope this helps.

ws

[gian]

Thank you SO much @wstevens01 . That worked!

I just nested that into another ifelse to aoid the ‘%’ to still show up even if the conversion is not applicable. see below.

Final calculation:

ifelse(
    isNotNull({CVR dyn. dec.}),
    ifelse({CVR dyn. dec.} >= 10, 
        concat( split(toString({CVR dyn. dec.}), ".", 1), "%"), 
        concat( substring(toString({CVR dyn. dec.}), 1, 3 ), "%")
    ),
    NULL
)