Use this feature to accomplish simple on-the-fly calculations, or to apply complex formulas to your data.
Examples of commonly used calculated fields include:
- Estimated Profit: [Billable Amount] - [Est. Cost]
- Profitability shown as a (%): (1 - [Est. Cost]/[Billable Amount])
To add a Calculated field, click the "+ Add Calculated Field" option to the left of your screen.
In the pop-up, provide a name for your calculated field:
While you can type your desired formula into the field, you need to use the the exact same formatting shown in the data table (in Camel Case, without spaces, and using the exact same spelling as shown).
It will likely be easier to instead use the "lightbulb" icon to select the options you want to use in your Calculated Field:
This will work for adding both Fields, as well as Expressions, into your Calculated Field.
Here we're creating a Profit field, so we're deducting the total Estimated Cost (internal) of the time entry from the total Billable Amount (external).
Next, indicate the Data Type (which is "Numeric", as both of the fields we are using are "Numeric"):
You can then use the "Preview" option to make sure your formula appears as expected.
Click "OK" to proceed. If you get an error but your formulas and syntax look correct, try clicking "OK" any way.
Once the Calculated Field has been created, it will show up at the bottom of your available fields at the left in a new "Calculated Fields" section. You can now drag/drop that field into your report:
Like other fields, you can then use the "Field Properties" option further customize how the field displays.
If your organization uses Custom Fields (more details here), you will be able to include those in Calculated Fields.
You will know it is a Custom Field because the formatting will say the association and then the name of the Custom Field, separated by a colon:
This can be useful in several scenarios, such as if you have a Custom Field on the Project level that indicates the Billing or Cost Rate should be adjusted.
- The values you create in custom fields in the BI&A tool will NOT be reflected in the rest of your ClickTime account
- Calculated fields must be created for each report as needed. There is not currently a method to re-use a Calculated Field from one report without building it in the new report
|+||expression + expression|
|-||expression - expression|
|/||expression / expression|
|<||expression < expression|
|<=||expression <= expression|
|<>||expression <> expression|
|=||expression = expression|
|>||expression > expression|
|>=||expression >= expression|
|AND||boolean_expression AND boolean_expression|
|BETWEEN||BETWEEN (test_expression, begin_expression, end_expression)|
|CASE WHEN...THEN...ELSE...END||CASE WHEN (boolean_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END|
|CASE...WHEN...THEN...ELSE...END||CASE (input_expression) WHEN (when_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END|
|CAST...AS||CAST (expression AS data_type)|
|CONCAT||CONCAT (expression, expression[,expression...])|
|CONVERT||CONVERT (data_type [( length)], expression[, style])|
|DATEADD||DATEADD (datepart, number, expression)|
|DATEDIFF||DATEDIFF (datepart, startdate, enddate)|
|DATEPART||DATEPART (datepart, date)|
|DISTINCT||DISTINCT (column) or DISTINCT column|
|IF...THEN...ELSE...END||IF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END|
|IIF||IIF (boolean_expression, true_expression, [false_expression])|
|ISNULL||ISNULL (check_expression, replacement_value)|
|OR||boolean_expression OR boolean_expression|
|ROUND||ROUND (expression, length[, function])|
|RUNNING||AVG RUNNINGAVG (column)|
|RUNNING||COUNT RUNNINGCOUNT (column)|
|RUNNING||SUM RUNNINGSUM (column)|