jQuery tablesorter sorting formatted currency

Question

EDIT, new code based on Mottie's answer:

jQuery.tablesorter.addParser({
    id: "monetaryValue",
    is: function (s) {
        return false;
    }, format: function (s) {
        return s.replace('$','').replace(/,/g,'');
    }, type: "numeric"
});
var tablezor = jQuery(".tablesorter");
tablezor.tablesorter({
        headers: {
            4 : { sorter: "monetaryValue" }
        },
        sortList: [[7,0]]
});

and HTML:

<table class="tablesorter">
<thead>
    <tr>
        ...
        <th>Processing Fees</th>
        ...
    </tr>
</thead>
<tbody>
{% for cs in customer_stats %}
    <tr class="js_striped">
        ...
        <td>
            <script>
                var options = {style:"currency", currency:"USD", minimumFractionDigits: 2,maximumFractionDigits: 2};
                document.write(new Intl.NumberFormat("en-US", options).format({{ customer_stats[cs]['processing_fee'] }}));
            </script>
        </td>
        ....
    </tr>
{% endfor %}
</tbody>
</table>

---

I've tried a dozen examples to fix this issue, and nothing is working.

I'm using jQuery.tablesorter to sort a table, and the currency columns will not sort correctly when I format the numbers as currency (US Dollars, in the format $1,945.00)

It's a Django template, and the template variables render as unformatted numbers like 1945.0, so I need to add formatting using javascript. I wanted to use the Humanize template filters library, but we use Jinja, which doesn't have that. I need to do the formatting on the client, so python suggestions probably aren't going to work.

When it's just a number, it sorts fine. When I use a little script tag (yes, I know this isn't the best way, it's a short-term fix untill we re-write the front-end with Backbone) to format the number as currency, the sort doesn't work. It sorts like this:

$3,380.00

$350.00

$353.24

$3,535.24

etc.

here's the function to format as currency:

function formatDollar(num) {
    var p = num.toFixed(2).split(".");
    return '$' + p[0].split("").reverse().reduce(function(acc, num, i, orig) {
        return  num + (i && !(i % 3) ? "," : "") + acc;
    }, "") + "." + p[1];
}

Here's the HTML:

<table class="tablesorter">
    <thead>
        <tr>
            ...
            <th class="{sorter:'monetaryValue'}">Processing Fees</th>
            ...
        </tr>
    </thead>
    <tbody>
    {% for cs in customer_stats %}
        <tr class="js_striped">
            ...
            <td>
                <script>
                    var money = formatDollar({{ customer_stats[cs]['processing_fee'] }});
                    document.write(money);
                </script>
            </td>
            ....
        </tr>
    {% endfor %}
    </tbody>
</table>

and the custom parser for tablesorter:

var tablezor = jQuery(".tablesorter");
tablezor.tablesorter({
        sortList: [[1,1]]
});
tablezor.addParser({
    id: "monetaryValue",
    is: function (s) {
        return false;
    }, format: function (s) {
        return s.replace('$','').replace(/,/g,'');
    }, type: "numeric"
});

Feel free to tell me how terrible any of this is; Any and all criticizm is welcome if you can tell me how to get tablesorter to sort currency correctly.

Thanks,

Answer 1

The solution to my problem was to do the formatting on the server.

May this be a lesson to not put hacky crap in my markup.

For the record, in my Django view, I did this:

customer_stats[c['customer']]['processing_fee'] = '${:,.2f}'.format(float(c['processing_fee']))

Then I removed the custom parser and used this:

tablezor.tablesorter({
    sortList: [[4,1]],
    textExtraction: function(node){ 
        return $(node).text().replace(/[,$£€]/g,'');
     }
});

The textExtraction took care of the parsing problem.

Thanks again Mottie for all the effort.