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
Calculated Field Limitations
Frequently Used Columns and Formulas
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.

Calculated Field Limitations

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)

Frequently Used Columns and Formulas

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

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)