Sorry to disappoint you but unfortunately that is not possible with built in features of plugin. At the moment only integers and float columns can have this functionality.
This is the common request from our users and we are looking to implement this feature for some of our next updates of the plugin.
There are several limitations when using formula columns in wpDataTables:
One formula column cannot be used in another. This limitation can be avoided by using nested calculations.
In tables with server-side processing formula, columns cannot be used for grouping, filtering, and sorting. Basically, formulas are calculated only for the rows that are immediately visible; Therefore, it’s not possible to “know” the values for currently invisible rows. Please note that this also applies to the “Manual” tables, as they also use server-side processing by default. Also of course, it is not possible to edit the cells generated by formulas in the editable tables.
Adding a sum in the sum row for formula columns is not yet supported.
We will also look to implement the same features as for the basics columns so these features will be available as soon as we find some proper solution. It is not very easy as it looks because like it is replied below it’s not possible to “know” the values for currently invisible rows.
A nested function uses a function as one of the arguments. For example Excel allows you to nest up functions.
Users typically create nested functions as part of a conditional formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0). The AVERAGE and SUM functions are nested within the IF function.
We do have option to do the calculation on data in your table via formula column but it can't do all of the formula that Excel can because the formulas in wpDataTables are NOT THE SAME as formulas in Excel.
OK, so it's only possible to calculate in columns (formula column) and not in rows, right? i.e. I'm not able to create something like a "subtotal row" or a formula for different kinds of calculations within a cell?
Yes the Calculation features will only work for the columns, if you want to calculate the subtotal row then our suggestion is to use the formula, where in formula rule you will add all of the columns from which you want calculate, you can have as many formula columns as you like, just change the column name of the formula so it will be obvious.
I was looking into this as well. I was able to grab the shortcode for getting the sum of a column and made a guess at what my table ID and column was by looking at a previous integer column ID. I was able to get it even for the formula field. The sum value for the formula field shows up while the table loads, then sets to zero when the table is fully loaded. It's so close!
Hi there, is there a way (or a workaround) to calculate the total / average / min / max within formula columns?
Best
Nick
HI Nick,
Thank you for your purchase.
Sorry to disappoint you but unfortunately that is not possible with built in features of plugin. At the moment only integers and float columns can have this functionality.
This is the common request from our users and we are looking to implement this feature for some of our next updates of the plugin.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Hi Bogdan,
OK - that's a pity but I really look forward to this upcoming feature.
And is there a way to sort formula columns?
Best
Nick
Hi Nick,
From our documentation:
There are several limitations when using formula columns in wpDataTables:
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
OK, thanks. Is this (sorting / filtering / grouping) something you'll add in a future release? Or will this never be supported?
Another question: what is a nested calculation? Is there a "how to" in your documentation?
Best
Nick
HI Nick,
We will also look to implement the same features as for the basics columns so these features will be available as soon as we find some proper solution. It is not very easy as it looks because like it is replied below it’s not possible to “know” the values for currently invisible rows.
A nested function uses a function as one of the arguments.
For example Excel allows you to nest up functions.
Users typically create nested functions as part of a conditional formula. For example, IF(AVERAGE(B2:B10)>100,SUM(C2:G10),0).
The AVERAGE and SUM functions are nested within the IF function.
We do have option to do the calculation on data in your table via formula column but it can't do all of the formula that Excel can because the formulas in wpDataTables are NOT THE SAME as formulas in Excel.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
OK, so it's only possible to calculate in columns (formula column) and not in rows, right? i.e. I'm not able to create something like a "subtotal row" or a formula for different kinds of calculations within a cell?
Hi Nick,
Yes the Calculation features will only work for the columns, if you want to calculate the subtotal row then our suggestion is to use the formula, where in formula rule you will add all of the columns from which you want calculate, you can have as many formula columns as you like, just change the column name of the formula so it will be obvious.
Best regards.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
I was looking into this as well. I was able to grab the shortcode for getting the sum of a column and made a guess at what my table ID and column was by looking at a previous integer column ID. I was able to get it even for the formula field. The sum value for the formula field shows up while the table loads, then sets to zero when the table is fully loaded. It's so close!
https://frugal-millennial.com/net-worth-calculator/
Example shortcode: [wpdatatable_sum table_id=3 col_id=34]