Examples for Formulas Using Data Engine Syntax Mode
The following examples describe typical scenarios for using formulas.
| • | Extracting a Month From a Date |
| • | Aggregate Values of All Rows |
| • | Concatenating Text Columns |
| • | Using an “if” Statement |
Extracting a Month From a Date
The function that returns any part of a date or date/time value is dateFormat:
|
Item |
Description |
|
Function |
dateFormat |
|
Syntax |
dateFormat(date_value, 'format') |
|
Example |
orderDate 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( [orderDate], 'MM') and call it orderMonth. The new column will list the month number of order date (i.e. 01 for January, 02 for February, etc.) |
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 product's inventory to all products. All aggregate functions start with agg_.
|
Item |
Description |
|
Function |
agg_ |
|
Syntax |
agg_sum(value) |
|
Example |
quantityInStock is a number data type and we need to calculate the percentage of each product's quantity to the entire inventory of products. We can create a new formula as: [quantityInStock]*100/agg_sum( [quantityInStock]) and call it quantityPercent. |
The simple + sign can be used to stitch strings of text together. An example would be creating full name from first and last name columns.
|
Item |
Description |
|
Function |
+ |
|
Syntax |
value1 + value2 + value3... |
|
Example |
contactFirstName and contactLastName 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: [contactLastName]+' , '+ [contactFirstName] and call it contactName. The returned result for a contact with first name ‘John’ and last name ‘Doe’ will be ‘Doe, John’. |
You can use “if” and other scripting functions to create more sophisticated formulas. For example, suppose you want to create a formula that determines whether your shipping department shipped customer orders within three days. To do this, you would create an if statement that compares two columns in your ERP system, orderDate and shippedDate. If the order was shipped within three days, it returns the word, “fast”. If not, it returns, “slow”. In the example below, notice that the isNull() statement is used to provide some error-handling by changing a null value into a date value (now).
|
Item |
Description |
|
Function |
if |
|
Syntax |
if (condition) {return result1;} else {return result2;} |
|
Example |
orderDate and shippedDate are date type columns that contain order and shipping dates in an ERP system. We want to decide in which cases our shipping department did a good job and for which orders it reacted too slow. |
|
Function Example |
if(dateDiff( [orderDate],isNull([shippedDate],now()),'DAYS')<=3) {return 'fast';} else {return 'slow';} First, make sure that _null_ values of shippedDate are replaced with a datavalue (now) and then run a comparison to get the result fast when shipping happened within three days of taking the order and slow in all other cases. |
Note: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.