Missing comma separators and adding them back in

<p>Good morning, all! I've run into a formatting dilemma that really has me scratching my head.... I've designed a line chart that pulls in values from a table and the 'Use 1000s separator' box is checked in the 'Format Number' area. In the chart, I have the 'Mouse Over' set to display the value pulled in from the table, along with a date also passed from the table. For the life of me, I can not get the value to display with commas. Here are three ways I've tried in the Expression Builder to fix the issue (none have worked):</p>
<p> </p>
<p><strong>1. (x.toLocaleString('en-US', { style: 'currency', currency: 'USD' }))</strong></p>
<p> </p>
<p><strong>2. function numberWithCommas(x) {</strong></p>
<div><strong> return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');</strong></div>
<div><strong> }</strong></div>
<div> </div>
<div><strong>3. x.toLocaleString()</strong></div>
<div> </div>
<div>Any ideas? It's not a huge matter to most users, but now it's become a personal battle for me..... I <em><strong>WILL </strong></em>have comma separators...... Of course, it's probably some little thing I've overlooked, and will feel rather dumb when it's pointed out.</div>
<div> </div>
<div>As always, thanks in advance for any help provided!</div>
<div> </div>
<div>Have a great day!</div>
<div> </div>
<div>Scott</div>

Find more posts tagged with

Comments

Matthew L.

<p>Hello Scott,</p>
<p> </p>
<p>I was able to produce results using the following (source: <a data-ipb='nomediaparse' href='https://stackoverflow.com/a/14428340'>https://stackoverflow.com/a/14428340</a>):</p>
<p>(row["MYCOLUMNNAME"]).toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,')</p>
<p> </p>
<p>Attached is an example.</p>

CodeRookie

<p>Thanks, Matthew! I will give it a shot.</p>

CodeRookie

<p>Matthew,</p>
<p>I tried your suggestion, but still couldn't get commas in my values. Here is what I actually have in the Expression Builder:</p>
<p> </p>
<div><strong>var1 = BirtDateTime.month(row["order_date"]);</strong></div>
<div><strong>var2 = BirtDateTime.day(row["order_date"]);</strong></div>
<div><strong>var3 = BirtDateTime.year(row["order_date"]);</strong></div>
<div> </div>
<div><strong>var1 + '/' + var2 + '/' + var3 + ('<br>') + '$' + (row["Sales"]).toFixed(0).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');</strong></div>
<div> </div>
<div>The reason I'm having to break the date down by component is that even though I'm passing it in from the table as mm/dd/yyyy, it somehow will spit out a datetime value. I don't think that is causing the issue. So, it's still a mystery why I can't add commas. I'm sure it doesn't matter, but I'm using v4.4.0 of Designer.</div>
<div> </div>
<div>Thanks,<br>
Scott</div>

Matthew L.

<p>I think the reason the regex I found fails is because you are removing the decimal places by using toFixed(0).</p>
<p> </p>
<p>Try this:</p>
<div>
<pre class="_prettyXprint _lang-js">
var1 = BirtDateTime.month(row["PAYMENTDATE"]);
var2 = BirtDateTime.day(row["PAYMENTDATE"]);
var3 = BirtDateTime.year(row["PAYMENTDATE"]);

var1 + '/' + var2 + '/' + var3 + ('<br>') + '$' + ((row["AMOUNT"]).toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,')).split(".")[0];
</pre>
</div>
<p> </p>

Matthew L.

<p>Looking at your original post again, the regex you provided in #2 works for me as well and is probably a better solution:</p>
<p> </p>
<div>
<pre class="_prettyXprint _lang-js">
var1 = BirtDateTime.month(row["order_date"]);
var2 = BirtDateTime.day(row["order_date"]);
var3 = BirtDateTime.year(row["order_date"]);

var1 + '/' + var2 + '/' + var3 + ('<br>') + '$' + (row["Sales"]).toFixed(0).replace(/\B(?=(\d{3})+(?!\d))/g, ',');
</pre>
</div>
<p> </p>

CodeRookie

<p>Matthew,</p>
<p>The solution you gave in 11:26am post worked perfectly! Thanks so much for the help! I will definitely add that snippet of code to my 'Bag of Tricks' cheat sheet for future reference..... LOL!!</p>
<p> </p>
<p>Have a great day!</p>
<p> </p>
<p>Scott</p>