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.
When using Calculated Fields, please be aware of the following limitations:
- Calculated fields are only available on a per-report basis. You must create any calculated fields you want to use for your report when designing that report.
- Calculated Fields can only reference data in its original format.
- Ex) BI&A lets you change how values are displayed. For example, you can display numbers as a %. However, calculated fields CANNOT use that display format (eg, to use a % as a multiplier)
- The values you create in custom fields in the BI&A tool will NOT be reflected in the rest of your ClickTime account
- Not all calculated fields can also be used as filters. You can using calculated field as a filter under two conditions:
- You built your formula using data that also can filter reports (eg: PersonActiveStatus, ProjectName)
- The results of your formula are inherently filterable (eg: True/False values)
|Desired Value||BI&A Formula|
|Billable Amount||No calculated field needed.
Use “TimeEntryBillableAmount” field.
|Month and Year||No calculated field needed.
Choose TimeEntryDate field and Field Properties > Format choose mm-yyyy or [Month Name] - yyyy
|Week and Year||No calculated field needed.
Choose TimeEntryDate field and Field Properties > Format choose Week Number
|Quarter||No calculated field needed.
Choose TimeEntryDate field and Field Properties > Format choose yyyy- Qtr or Qtr
|Profit||[TimeEntryBillableAmount] - [TimeEntryCostAmount]|
|Billable Profit||CASE WHEN ( [ClickTime].[dbo].[TimeData].[TimeEntryIsBillable] = 'true' ) THEN ([ClickTime].[dbo].[TimeData].[TimeEntryBillableAmount] - [ClickTime].[dbo].[TimeData].[TimeEntryCostAmount]) else 0 end|
|Cost Amount (i.e., the total cost of the associated hours)||No Calculated field needed.
Use “TimeEntryCostAmount” field
|+||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)|