Creating Calculated Fields (Formulas)

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])

Adding a Calculated Field to a Report (In Design Mode)
Using your ClickTime Custom Fields
Available Calculated Field Expressions

Adding a Calculated Field to a Report (In Design mode)

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.

Using Your ClickTime Custom Fields

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.

Please note:

• 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

Available Calculated Field Expressions

Function	Name Syntax
+	expression + expression
-	expression - expression
/	expression / expression
<	expression < expression
<=	expression <= expression
<>	expression <> expression
=	expression = expression
>	expression > expression
>=	expression >= expression
AND	boolean_expression AND boolean_expression
AVG	AVG (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])
COUNT	COUNT (expression)
DATEADD	DATEADD (datepart, number, expression)
DATEDIFF	DATEDIFF (datepart, startdate, enddate)
DATEPART	DATEPART (datepart, date)
DISTINCT	DISTINCT (column) or DISTINCT column
GETDATE	GETDATE ()
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)
LEN	LEN (expression)
MAX	MAX (expression)
MIN	MIN (expression)
OR	boolean_expression OR boolean_expression
ROUND	ROUND (expression, length[, function])
RUNNING	AVG RUNNINGAVG (column)
RUNNING	COUNT RUNNINGCOUNT (column)
RUNNING	SUM RUNNINGSUM (column)
SUM	SUM (expression)