Format for Currency in calculation

It would be great if we could do a calculation with whole numbers, that if a whole number, it would add .00 to the end, or whatever amount of 0’s we choose.

In Perl the syntax is:

sprintf(’%.2f’, “number to format”); example:
sprintf(’%.2f’, “453.013”); // would make it: 453.01
sprintf(’%.2f’, “453.019”); // would make it 453.02

making it have a dollar sign would be as easy as:
sprintf(’$%.2f’, “number”);

Php is easy too:
number_format($number, 2, ‘.’, ‘’);
or even:

<?php $formatter = new NumberFormatter('en_US', NumberFormatter::CURRENCY); echo $formatter->formatCurrency(-0.99, 'USD'), PHP_EOL; $formatter = new NumberFormatter('en_CA', NumberFormatter::CURRENCY); echo $formatter->formatCurrency(-0.99, 'USD'), PHP_EOL; ?>

Output from PHP 5.5:


Output from PHP 5.6:


so having a calculation be able to do it would be great.

Hey, @bizactuator.

We actually can do this using a calculation. It’s not as simple as PHP, but it’s not too much more complex, either. We use numberformat() to format the number (thousands separator, decimal separator, currency symbol pre- or postfix) and then use some regex to check how many trailing zeroes we need to add to pad it.

We can’t use something like fixed() directly, which would be easier, because a formatted number is a string rather than a number. So, we need to treat it like a string and instead concatenate trailing zeroes, if necessary.

In a calculation, that would look something like

total = numbervalue({{ key }});

formatted = numberformat(round(total, 2), ",", ".", "$", "");
padded = if(regexmatch(formatted, "\\.[0-9]{2}"), formatted, if(regexmatch(formatted, "\\.[0-9]{1}"), formatted || "0", formatted || ".00"));


For an input of $1.2, that would output $1.20, for example.

Hope this helps!