Post

7 followers Follow
0
Avatar

Format $ Currency in calculation

Trying to format the following in a calculation so that the output is $AUD XX,XXX

"$AUD" + ((((@List Goal*.20).5).35)*24000)

However get a error message "The type of result changed from number to text."

Anyone know how to format number so that we can place a currency symbol in front?

James Hayward

Please sign in to leave a comment.

12 comments

1
Avatar

Hey James

Something like this should work:

"$" + (@number1 + @number2)

But one problem with this is that you lose control of the number of decimal places that are displayed, because this is not an option in calculations with text output. You can control this by using a javascript function toFixed() like this:

"$" + (@number1 + @number2).toFixed(2)
The brackets are important in both cases (they should be around the numbers). The 2 inside the brackets at the end is the number of decimal places you need.

Cheers,
Sara - Podio

Sara Høeg Højlund-Rasmussen 1 vote
0
Avatar

The answer from Sara shows how to get decimal places, but what about getting the comma's in the right places? So 2800 becomes 2,800?

Brett Bernstein 0 votes
0
Avatar

The answer to Brett's question

"$" + (@number1 + @number2).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");

I found it on the web, couldn't explain the regex, but it works ;)

Simon Rimmington 0 votes
0
Avatar

I am using this method but Podio continues to claim there is an error and nothing I try works. It says "The type of result changed from number to text." and won't allow me to proceed.

 

This is what I'm using. "$" + ( @Amount - @Payment 1 - @Payment 2 - @Payment 3 - @Payment 4 - @Payment 5 )

If I use the formula without using "$" +  it calculates fine. 

Any ideas?

Thanks.

Brett Golden 0 votes
1
Avatar

Hi Brett,

"Calculations will output data in one of three different types: Number, Date, or Text. The output type is determined the first time you create your calculation field. This is important to note, since the output type cannot be changed. If you try to change from one output type to another, you will see an error that says "The type of result changed from [X] to [Y]." To fix this, you simply need to delete the calculation field and recreate it to get a new output type. "  https://help.podio.com/hc/en-us/articles/201019358-Calculations

"$" + 
makes the result a string (text) , w/o it's a number.

Rainer

Rainer Grabowski 1 vote
0
Avatar

Rainer,

I'm having the same issue. If I use the Set Unit option, it puts the $ sign at the end of the calculation. Your solution solved the $ sign issue, but not I lose the comma for any total over $999 as it seems to be reading as text, not a number. How can I fix that?

Chris North 0 votes
0
Avatar

Hi Chris,

there's no need to add $ behind the number directly in the calculation. In the template editor you see in each field at the left side a small triangle-icon for some settings. Click it, then click "Unit" and enter $. That keeps the field a number type calc. field (good for further calculations with that number). But do it in a fresh calc field, yours is now string type (s. my last posting above).  

For a number that is now a string (like yours) you can format it with 1000-separators and decimal points this way:

@number.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "$"  =  eg. 1,001 $ (w/o decimals)
@number.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",") + $ = e.g. 1,000.00 $

Rainer

 

Rainer Grabowski 0 votes
0
Avatar

Hello,

I am using this sort of calculation and was wondering if there is anyway to show the negative value instead of NaN.

This is my calculation:

"**" + "$" + " " +(@Total Billable Budget Dollars-@Total Actual Billable Dollars).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")+"**"

 

It works fine if the result is positive.

Janiece Rice 0 votes
0
Avatar

Hi Janiece,

usually there isn't a difference between positive and negativ results, works the same way. NaN means: The result is Not a Number. 

Try this: directly after your code (in the same line) add: || 123
If the result is 123 or if you still get the NaN-notification one of the number variables isn't ok.

Then try this to parse both variables to numbers: 
(Number(@Total Billable Budget Dollars) - Number(@Total Actual Billable Dollars))

Which kind of field are @Total Billable Budget Dollars and @Total Actual Billable Dollars - number fields, money fields, calculated fields? What is shown if you enter only @Total Billable Budget Dollars or @Total Actual Billable Dollars in a new line at the end of your code?

Rainer

Rainer Grabowski 0 votes
0
Avatar

Try this: directly after your code (in the same line) add: || 123
If the result is 123 or if you still get the NaN-notification one of the number variables isn't ok.

The above resulted in the NaN-notification

Then try this to parse both variables to numbers: 
(Number(@Total Billable Budget Dollars) - Number(@Total Actual Billable Dollars))

The above resulted in "your result is not a number".

Which kind of field are @Total Billable Budget Dollars and @Total Actual Billable Dollars - number fields, money fields, calculated fields? What is shown if you enter only @Total Billable Budget Dollars or @Total Actual Billable Dollars in a new line at the end of your code? - Tried this also.  The result is "result changed from text to number".

 

Total billable budget dollars is a money field.

Total actual billable dollars is a calculation field with the below calculation that works fine:

var status = @All of Billable/Non-Billable;
var dollars = @All of Dollars;
var sum = 0;
for(var i = 0; i < status.length; i++)
{if(status[i] == "Billable")
{"$" +" " +(sum += dollars[i]).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")}}

The reason I am doing the above is we have some really picky accountants that I am trying to persuade to stop using excel spreadsheets manually and use these BTA apps instead.  But they want formatting, dollar signs, etc...

That is what I was originally trying to do with the Total Budget Variance.  If I use normal tokens then everything is fine.

@Total Billable Budget Dollars-@Total Actual Billable Dollars

The above by itself results the negative number if needed.

But as soon as I start trying to add any sort of formatting then it no longer works correctly.

 

Thank you Rainer.  I really appreciate your help.

Janiece Rice 0 votes
0
Avatar

Hi Janiece,

I'm a bit astonished that your calculation for "Total actual billable dollars" shows $ amount (like $ 1,000.00). It should only show 1,000.00.

"$" +" " +(    and .toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")
are superflous and do nothing.

What is shown if you enter only @Total Billable Budget Dollars or @Total Actual Billable Dollars in a new line at the end of your code? - Tried this also.  The result is "result changed from text to number".
I've meant: What is shown in the field preview under the field?  (You don't need to click Done.)

Can you please share some screenshots (of a scenario where the result is negative and doesn't work) where I can see
A) Item view, fields "Total Billable Budget Dollar" and "Total Actual Billable Dollars"
B) Modify Template view, calculation fields:  "Total Actual Billable Dollars" and "Total Budget Variance".

Rainer

 

 

Rainer Grabowski 0 votes