Use this feature to accomplish simple onthefly 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 popup, 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.
When using Calculated Fields, please be aware of the following limitations:
 Calculated fields are only available on a perreport 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 mmyyyy 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) 
Comments
0 comments
Article is closed for comments.