1. Feature Overview
SIERA Expression Builder tool lets Admins create their own expressions that can be used to easily retrieve and manipulate data on any report. This is an evolving tool and presently helps users write basic aggregate functions with multi-conditions.
2. Prerequisites
SIERA is a paid feature. To enable it for your account, contact your account manager or support@leadsquared.com.
3. Expression Builder Rules
- A field in the expression builder has to be enclosed with backticks. For example,
`entity.field_name`. - The field name has to be appended with the entity. For example,
`Lead.ProspectStage`. - The values for a string have to be in single quotes. For example,
'string'. - An array of values must be enclosed in square brackets. For example,
['Value 1', 'Value 2']. - SIERA stores empty date values as 01-01-1925. If you want to write an expression with null date values excluded, use
ToYear(<date field>) > 1925. Similarly,ToYear(<date field>) <= 1925will filter rows with date values not defined. For example,CountDistinctIf(`Lead.ProspectID`, ToYear(`Lead.mx_date_of_birth`) > 1925)will return leads who have a defined date of birth.CountDistinctIf(`Lead.ProspectID`, ToYear(`Lead.mx_date_of_birth`) <= 1925)will return leads where the date of birth is not defined.
- Clicking on a tab will enter the selected function from the expression builder.
4. Writing an Expression
This section will help you understand the syntax and usage of various functions available for building expressions.
Inside the Report Builder, navigate to the Data Section, click on + Values, and then click on + Custom Expression.
4.1 Operators, Conditions, and Constants
| Function & Example Expression |
| Equal – Used to compare if 2 values or fields are equal.
|
| Not Equal – Used to compare if 2 values or fields are not equal.
|
| Less Than – Used to compare if a value or field is less than compared to another value or field.
|
| Less Than or Equal – Used to compare if a value or field is less than or equal to another value or field.
|
| Greater Than – Used to compare if a value or field is greater than compared to another value or field.
|
| Greater Than or Equal –Used to compare if a value or field is greater than or equal to another value or field.
|
| in – Used to determine whether the value exists in a list of values.
|
| notin – Used to determine whether the value does not exist in a list of values.
|
| Text Constant – Text constant to be written as ‘Text’, with quotations.
|
| Number Constant – Text constant to be written as 50, without quotations.
|
| Addition – Used to add two values in your report. Fields (Number type) and constants can be used as operands
|
| Subtraction – Used to subtract two values in your report. Fields (Number type) and constants can be used as operands.
|
| Multiplication – Used to multiply two values in your report. Fields (Number type) and constants can be used as operands.
|
| Division – Used to divide two values in your report. Fields (Number type) and constants can be used as operands.
|
4.2 Logical Expressions
| Function & Example Expression |
| and – An and expression returns true if all of its operands are true. The and expression will work if there is more than 1 expression inside it. For example, let’s say you want to return TRUE when a Lead has converted (Stage is Customer) and if the Source is either from Referral or Affiliate.
|
| or – An or expression returns true if any of its operands are true. The or expression will work if there is more than 1 expression inside it. For example, let’s say you want to return TRUE when a Lead has converted (Stage is Customer) or if the Sub-stage is either Converted or Paid.
|
4.3 Aggregate Expressions
| Function & Example Expression |
SUM The Sum function allows you to calculate the sum of numeric values within a given field. For example, if you want to create the calculated column which stores the sum of the field mx_Custom_2. We can use the Expression Builder to write the expression.
|
| SumIf The SumIf function allows you to calculate the sum of numeric values within a given field along with some conditions. For example, if you want to create the calculated column which stores the sum of the field mx_Custom_2 when Stage is “Customer”. We can use the Expression Builder to write the expression as given.
|
| Count The Count function lets you count the number of occurrences of a specific value or field within a given range. For example, let’s assume you want to create the calculated column which stores the count of the leads.
|
| CountIf The CountIf function allows you to count the number of occurrences that meet a specific condition. For example, you can create a calculated column that stores the count of the leads when the lead stage is ‘Prospect’.
|
| CountDistinct The CountDistinct function allows you to count the unique number of occurrences that meet a specific condition. For example, you can create a calculated column that stores the count of unique leads.
|
| CountDistinctIf The CountDistinctIf function allows you to count the distinct number of occurrences that meet a specific condition. For example, you can create a calculated column that stores the count of distinct leads when the lead stage is ‘Prospect’.
|
| Max The Max function allows you to calculate the maximum of numeric values within a given field. For example, you can create the calculated column which stores the maximum of the field mx_Custom_2.
|
| Min The Min function allows you to calculate the minimum of numeric values within a given field. For example, you can create the calculated column which stores the minimum of the field mx_Custom_2.
|
| Avg The Avg function allows you to calculate the average of numeric values within a given field. For example, you can create the calculated column which stores the average of the field mx_Custom_2.
|
| MaxIf The MaxIf function allows you to calculate the maximum of numeric values within a given field along with some conditions. For example, you can create the calculated column which stores the maximum of the field mx_Custom_2 when Stage is ‘Prospect’.
|
| MinIf The MinIf function allows you to calculate the minimum of numeric values within a given field along with some conditions. For example, you can create the calculated column which stores the minimum of the field mx_Custom_2 when Stage is ‘Prospect’.
|
| AvgIf The AVGIF function allows you to calculate the average of numeric values within a given field along with some conditions. For example, you want to create the calculated column which stores the average of the field mx_Custom_2 when Stage is ‘Prospect’.
|
4.4 Date Time Expressions
| Function & Example Expressions |
| DurationAdd The DurationAdd function allows you to add any duration to a particular date field. Allowed Units – second, minute, hour, day, week, month, quarter, year Returns – Added Duration (DateTime) For example, you can create the calculated expression which counts the lead which came into the system 4 months ago and the current stage is not ‘Customer’.
|
| DurationSubtract The DurationSubtract function allows you to subtract any duration from a particular date field. Allowed Units – second, minute, hour, day, week, month, quarter, year Returned Value – Subtracted Duration (DateTime) For example, you can create the calculated expression which counts the lead which came into the system 4 months ago and the current stage is not ‘Customer’.
|
| CurrentDateTime The CurrentDateTime function returns the current date and time. For example, you can create a calculated column that stores -4 months from date field mx_Custom_2.
|
| ToHour The ToHour function allows you to extract the hour component of any datetime(timestamp) field. Returned Value: Hour Component (Number) eg: 23 For example, you can write a condition where you want to count the leads which came into the system at 11 PM.
|
| ToYear The ToYear function allows you to extract the year component of any datetime(timestamp) field. Returned Value: Year Component (Number) eg: 2023 For example, you can write a condition where you want to check if the year component of the datetime field is equal to 2023.
|
| ToYearWeek The ToYearWeek function allows you to extract the week and year components of any datetime(timestamp) field. Returned Value: Week and Year Component (1-53 Number) Eg: 202345 (45th Week of 2023) For example, you can write a condition where you want to check if the week and year component of the datetime field is equal to the 45th Week of 2023.
|
| ToMonthOfYear The ToMonthOfYear function allows you to extract the month component of any datetime(timestamp) field. Returned Value: Month of the Year (1-12 Number) eg: 1 (for January) For example, you can write a condition where you want to check if the month component of the datetime field is equal to January.
|
| ToDateOfYear The ToDateOfYear function allows you to extract the date component of any datetime(timestamp) field. Returned Value: Date of the Year (1-366 Number) eg: 32 For example, you can write a condition where you want to check if the date component of the datetime field is equal to 2nd Feb.
|
| ToQuarterOfYear The ToQuarterOfYear function allows you to extract the quarter component of any datetime(timestamp) field. Returned Value: Quarter of the Year (1-4 Number) eg: 2 For example, you can write a condition where you want to check if the hour component of the datetime field is equal to 2nd Quarter.
|
| ToMonth The ToMonth function allows you to extract the month and year components of any datetime(timestamp) field. Returned Value: yyyy-mm-01 (String) eg: ‘2023-02-01’ For example, you can write a condition where you want to count the leads created in the month of February 2023.
|
| ToQuarter The ToQuarter function allows you to extract the quarter and year components of any datetime(timestamp) field. Returned Value: YYYY QX (String) eg: ‘2023 Q2’ For example, you can write a condition where you want to count the leads created in the 2nd Quarter of 2023.
|
| ToDayOfWeek The ToDayOfWeek function allows you to extract the day of the week component of any datetime(timestamp) field. Returned Value: 1-7 (Number) Range: (1-7: Monday = 1, Tuesday = 2, …, Sunday = 7) eg: ‘6’ for Saturday For example, you can write a condition where you want to count the leads created on Saturdays.
|
| ToWeek The ToWeek function allows you to extract the week and year components of any datetime(timestamp) field. Returned Value: YYYYWW (Number) eg: 202347 For example, you can write a condition where you want to count the leads created on the 47th Week of 2023.
|
| DateUnitDiff Returns the count of the specified time unit boundaries crossed between the start and end dates. This can be used to create TAT between 2 date fields. Returned Value: (Number) eg: 43
Accepted Intervals –
The above expression will calculate the TAT for each grouping on average. For cases when there is no value in the date field, the expression builder will take the value of 01-01-1925. To exclude cases where there is no payment done, use the syntax below-
|
| ToDate Returns the date component from a datetime field. Returned Value: (String) eg: 2023-10-03 For example, you can write a condition where you want to count the leads created on 2023-10-03
|
Any Questions?
If you have any questions related to Expression Builder that weren’t answered here, please leave comments below. We’ll be happy to help!
