Add new data source columns that calculate data from other fields. More data options in a data source means you can create more advanced and accurate formulas.
You can add calculated fields by opening a data source or its settings. Here are the available calculation functions:
DateDiff() – Calculate the time difference between two date-time fields.
Field() – Add, subtract, divide, or multiply values from fields in the same data source.
Today() – Include the current date and time in a calculation.
Calculated fields are supported in integration and manual data sources. They are not available in SQL or API data sources.
This function calculates the absolute time difference between two date fields in seconds.
Example
DateDiff(Field("Started at"), Field("Ended at"))
Consider dividing the calculation by 60 to get minutes or 3600 to see your data in hours:
DateDiff(Field("Created date"), Field("Closed date"))/60
DateDiff(Field("Created date"), Field("Closed date"))/3600
This function allows you to perform different calculations with the available data source fields.
Example
Field("Total time") / 60
Field("Price") + Field("VAT Amount")
This function returns the date and time of today. You can either use it as an independent statement – Today()
– or combine with the other two functions above.
Example
Today()
– returns the date and time of today. If the used fields contain empty values, the calculated field will also return empty values.
Field("End date", Today())
– returns the date and time of the field called End date. If the field contains empty values, the calculated field will return the date and time of today.
DateDiff(Field("Start date"), Today())
– returns the time difference from Start date to now (in seconds).
DateDiff(Field("Start date"), Field("End date", Today()))
– returns the time difference from Start date to End date. If End date contains empty values, the calculated field will return the date and time of today.
You can set a default value in your calculated field. Say you're adding Field A + Field B, and Field B is missing data in some rows.
If you don't add a default value, your new calculated field will also be empty.
If you add a default value, this value will fill out all the previously-empty rows.
Example
Field("Workspace ID",1234)
Field("End date", Today())
If you want to use a calculation that includes a decimal point (such as 2.6 or 30.075), use the full stop/period instead of a comma. This applies to all localizations.
The current syntax uses commas to manipulate arguments or separate one thing from another. Therefore, if you want to include a decimal number, please use the full stop.