Please wait...

Formula (calculated) columns

Video versionA video overview of formula (calculated) columns in wpDataTables

Formula (calculated) columns in wpDataTablesHow to create and use formula-based columns

If your dataset used for wpDataTable creation is not comprehensive – for example it shows only the price without VAT tax, or you need a column showing a result of calculation based on other columns cells values, you can use the formula (calculated) columns.

See this table: “Tax” and “Difference” columns do not exist in the dataset, but are calculated “on the fly” using the formula columns feature.

Please note that formulas in wpDataTables are NOT THE SAME thing with formulas in Excel!

Add formula columnCreating a formula column

Any table can have a formula column. To add one, you can click the “Add a formula column” button :

Formula Image

Formula constructorBuilding a formula and testing it

After you click on “Add formula a column” button you will see a popup formula constructor. It has a fixed name ‘formula_1’, ‘formula_2’, etc. – depending on the amount of formulas that you added.

The formula constructor popup has several main elements:

  1. Columns that can be used in the formula. Please note that only numeric (float and integer columns) can be used in formula columns.
  2. Formula.The created formula itself.
  3. Supported operators. Formulas in wpDataTables support all arithmetical operators, brackets to define the calculation order, and trigonometrical operators.
  4. Preview button and preview section. Once you prepare the formula you can click this button to see the result for first 5 rows of your table to make sure that the calculation is correct.

Additionally there is an explanation text on the top.

You can use columns (values for each cell will be inserted), or number values. Only numeric columns allowed (non-numeric will be parsed as 0). Basic math operations and brackets are supported. Example: col1*((col2+2)-col3*sin(col4-3)).

Formula image 3

To calculate the “Tax” column in the above example we can just use the catalog cost column value, multiply it by some number, and then click “Save“.

Formula image 4

Now let’s change the displayed header of the formula column. To do that click the button for column settings.

Formula Image 5

Enter the new value in the “Displayed header” input in the first tab column setting and click apply.

Formula image 6

If you want to change the formula for calculation you can go to “Data” tab and click on “Open formula editor” button.
After you click on “Open formula editor” button the formula constructor popup will appear. You can use it to adjust the formula that is used for calculation.

Formula image 7

Formula columns can be easily deleted by clicking the “Delete” button in the column header area in the live table preview.

There is a number of limitations when using formula columns in wpDataTables:

  1. One formula column cannot be used in another. But this limitation can be avoided by using nested calculations.
  2. 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 visible at the moment, so it’s not possible to “know” the values for currently invisible rows. Please note that it 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.
  3. Adding a sum in sum row for formula columns is not yet supported.

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.