Calculating column total with currency data

StefanR

Hi, quick pointer needed.
I'm successfully totalling a column that holds a currency value. We're reading the column data in this way:

var api = this.api(); var columndata = api.column(columnnum).data();

This returns the displayed value with currency formatting that we then process. I'm also injecting the raw number into the td cell via
data-order="1080.0"

Question: can I read the unformatted data-order value into columndata easily using the DataTables API?
If not is there another data attribute I could use, maybe something like data-value?

Any pointers appreciated. We currently strip the currency formatting from the string value that columndata holds but this is a pain as we introduce new currencies and formatting schemas.

For reference here is the top part of our footerCallback (no doubt I found this code on this site somewhere!)

`'footerCallback': function(tfoot, data, start, end, display) {
var api = this.api();
var columndata = api.column(columnnum).data();

            // Remove the £ sign to get integer data for summation
            var intVal = function(i) {
                return typeof i === 'string' ?
                    i.replace(/[\£,]/g, '') * 1 :
                    typeof i === 'number' ?
                    i : 0;
            };
            // Total over all pages
            fullTotal = api
                .column(columnnum)
                .data()
                .reduce(function(a, b) {
                    return intVal(a) + intVal(b);
                }, 0),
                // Total over this page
                pageTotal = api
                .column(columnnum, {
                    page: 'current'
                })
                .data()
                .reduce(function(a, b) {
                    return intVal(a) + intVal(b);
                }, 0);

[...]
`

bindrid

I am curious why columndata = api.column(columnnum).data(); returns formatted data. Are you setting its type somewhere? I would expect the data object to be pure numbers and use the render function or row created event to format the html cells.

StefanR

This data is seeded via HTML (the table is initially fully written to the page by a Rails page).
I think I see what you're saying - we could seed the raw data and then use a formatter?

I'd still like to know if the raw data can be read somehow.

StefanR

Just warming this up - we're still struggling a bit with this.
I understand the approach @bindrid made, however the datatable we render onto the page with Rails can vary in the amount of columns it has. This means that if I deploy a renderer I apparently have to target it by column index, this makes things quite tricky as the index can vary.
It may be workable but it's not ideal.

Generally speaking does anyone have any best practices at hand on how to sum up columns that hold currency values and which may have GBP £ one day and € Euro another?

Or put another way, is there a convenient way to assign cell renderers to a column without having to track its column index (as this may vary)?

rduncecb

You can retrieve columns by name(if that helps) via the api
https://datatables.net/reference/api/column()
using a column selector:
https://datatables.net/reference/type/column-selector

bindrid

So I have a table that contains money, in the millions, decimals integers. So what I ended up doing meshing this plugin https://github.com/autoNumeric/autoNumeric with datatable. As suggested before, calculations were done with the data object associated with a row then I used that plugin to format and display the numbers.

I used the className in columns setup to determine how the plugin should handle the numbers.

allan

Question: can I read the unformatted data-order value into columndata easily using the DataTables API?

Yes. You can return the orthogonal data using cells().render(). In this case you might use table.cells( null, columnnum ).render( 'sort' );.

You should then be able to do a simple sum of that data since it is unformatted (i.e. you don't need to deformat it).

This only works if you use orthogonal data though.

Allan

StefanR

thanks to both of you.
Stupid question, what's orthogonal data?

tangerine

https://datatables.net/manual/data/orthogonal-data#HTML-5

allan

Sorry - I assumed you knew that term since you are using data-order. Its really something that I've just used in the DataTables documentation to indicate that the data for a single cell can have multiple representations. Its detailed in the link @tangerine gave.

Allan