Report Builder

Formulas

You can find Formulas in the Add Chart section of Clockwork Report Builder. They can be used to create calculated columns that can be used like any other column of data.

Use Cases

Formulas can be used to segment data, to convert the data type of a field (such as converting a string to a date), to aggregate data, to filter results, to calculate ratios, and much more.

• Basic Components
• Examples
• Syntax
• Date and Time Patterns
• Supported Functions
• Creating a Formula
• Adding a Nested Formula
• Sample Formulas

Basic Components

Examples

Syntax: dateFormat(date_value, 'format')

Example: created_at is a date data type and we need to extract the month part of it to use on the x-axis of a bar chart.

We can create a new formula as dateFormat( [created_at], 'MM') and call it CreatedMonth. The new column will list the month number of created_at (i.e. 01 for January, 02 for February, etc.)

• Aggregate Values of All Rows

You can get the aggregate of a column based on all rows of data to use in a calculation. An example use case of this would be calculating the percentage of one Client Companies open project to all active projects at any one time. All aggregate functions start with agg_.

Syntax: agg_sum(value)

Example: ContractValue is a number data type and we need to calculate the percentage of each contract’s value to the entire collection of active projects. We can create a new formula as: [ContactValue]*100/agg_sum( [RetainedProjects]) and call it PercentAccountsPayable.

• Concatenating Text Columns

The simple + sign can be used to stitch strings of text together. An example would be creating Project name from Project Title and Client Company columns.

Syntax: value1 + value2 + value3...

Example: ProjectTitle and ClientCompany are text type columns and we want to concatenate these together for searches with the last name at the beginning to facilitate sorting. We can create a new formula as: [ClientCompany]+' / '+ [ProjectTitle] and call it Project Name. The returned result for a contact with Project Title ‘COO’ and Client Company ‘Azure’ will be ‘Azure/COO’.

• Using Other Scripting Functions

Painless scripting functions can be used to create calculated columns. An example of this use case would be to use the if function to return a value based on a condition.

Syntax: if (condition) {return result1;} else {return result2;}

Example: Closing Reason of “No Contract” and Project Flags of either “Presentation Only” or “Internal” should be excluded from the metrics for real projects run by the Firm. 

The Function:
if([Project Type] == "No Contract" || [Presentation] == "true" || [Internal] == "true"){
return "Internal, No Contract + Presentation Only";
}
return "True Projects";

Notes

1. When using the action commands to add functions and columns to the formula dialog (rather than typing it in) pay attention to the position of your cursor. Functions are added within parentheses and unless you move your cursor inside the parentheses before adding columns or typing, you may create a syntax error in your formula. Make sure you test your formula before saving it!
2. Even if a Painless function is not listed in the Functions list, it may still be supported. Don't hesitate to try. For example [created_at].dayOfWeek is a perfectly acceptable formula, although it's not listed. It returns the number of weekdays of the date value. Note that this example also illustrates the use of the Apply operator (.) as an alternative way to invoke a function.

Syntax

Date and Time Patterns

dateFormat function accepts a format argument that determines how the formatted date (and time) is going to be displayed. A quick reference exists in the Formula Builder when the function is selected, but here is a full reference of all possible values for format and an example of how it affects the date string.

• Note 1: You may use single, or double quotes around the format string.
• Note 2: All dates are treated as GMT in the Report Builder. Because of that, the three formats: z, Z, X return values for GMT.

Format strings can be combined to give you the format that you want. For example: dateFormat(“07/04/2001”, "EEE, MMM d, ‘’yy") results in: Wed, Jul 4, ‘01

*dateFormat reference: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Supported Functions

• Numeric: Elasticsearch Math API reference
• Text: Elasticsearch Text API reference
• Date: Supported functions include now, dateFormat, dateParse, dateAdd, dateSubtract, dateDiff, and dateIsNull.

Creating a Formula

1. To create a formula click on the uppercase sigma letter.
2. Click on Create Formula.
3. Look for the function you want to insert, choose it, check it and click Add to formula.
4. Look for the column you want to Insert and add it to the parentheses. Add any additional parameters that are needed for the function (e.g. dateFormat function needs a format parameter that can be set to 'MM' if the date is to be formatted as a month - or 'yyyy' if it has to be formatted as a 4-digit year)
5. Test the formula. Don’t forget to name your formula, and Save it.

Adding a Nested Formula

You can create nested formulas (formulas inserted within formulas) by copying and pasting the syntax of one formula into another one or by selecting one of the formula columns the same way you do to insert a regular dataset column into a formula’s syntax.

1. Just look for the formula column you want to insert and add it in the parentheses like you would with any other data column.
2. Add any additional parameters that are needed for the function.
3. Test the formula. Don’t forget to name and save your formula.

Sample Formulas

Close Reason Standardized:
if([closing_reason] == "No Contract"){
return "No Contract + Presentation Only";
}
if([closing_reason] == "Presentation Only"){
return "No Contract + Presentation Only";
}
return "True Projects";

Placement: 
if([closing_reason] == "Placement"){
return 1;
}
return 0;

Win/Lost:
if([closing_reason] == "Placement"){
return "Win";
}
if([closing_reason] == "Canceled"){
return "Lost";
}
if([closing_reason] == "Terminated"){
return "Lost";
}
if([closing_reason] == "Internal Hire"){
return "Lost";
}
return null;

No Contract:
if([closing_reason] == "No Contract"){
return 1;
}
return 0;

No Contract + Presentation Only:
if([closing_reason] == "No Contract"){
return 1;
}
if([closing_reason] == "Presentation Only"){
return 1;
}
return 0;

Project Type Segregation
if([project_type] == "retained"){
return "Regular Project";
}
if([project_type] == "retingency"){
return "Regular Project";
}
if([project_type] == "Contingency"){
return "Regular Project";
}
if([project_type] == "contingency"){
return "Regular Project";
}
if([project_type] == "Retained"){
return "Regular Project";
}
return "Alternative Project";

Project Count:
agg_distinctcount([project_id])

Presentation Only:
if([closing_reason] == "Presentation Only"){
return 1;
}
return 0;

True Projects:
if([closing_reason] == "No Contract"){
return 0;
}
if([closing_reason] == "Presentation Only"){
return 0;
}
return 1;

Total
agg_count([project_id])