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,
`data_source_name.field_name`. - The field name has to be appended with the entity. For example,
`Leads.Lead Stage`. - 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(`Leads.Prospect ID`, ToYear(`Leads.Date of Birth`) > 1925)will return leads who have a defined date of birth.CountDistinctIf(`Leads.Prospect ID`, ToYear(`Leads.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. Types of Expressions
Expressions in SIERA are of 3 types –
- Aggregated Expressions – An aggregate expression uses an aggregate function to summarize a selected metric. These include expressions like Count, CountDistinct, Sum, Min, Max, and Avg.
- For example, if we want to get a count of leads, we can use the expression
Count(<field name>).
- For example, if we want to get a count of leads, we can use the expression
- Conditional Expressions – Conditional expressions use aggregate functions along with conditions.
- For example, if you want to Count leads only if they are from Google and Facebook, using an expression of Count() will not work. In this case, we can use
CountIf(<condition>).
- For example, if you want to Count leads only if they are from Google and Facebook, using an expression of Count() will not work. In this case, we can use
- Calculated Values – Calculated Values are a combination of 2 or more expressions (aggregated or conditional) on arithmetic functions like +, –, *, or /.
- For example, if we want the percentage of leads from Google and Facebook over total leads, we can use
(Countif(<condition>)/Count(<field name>))*100. Calculated Values do not support drill-down in the reports or dashboards.
- For example, if we want the percentage of leads from Google and Facebook over total leads, we can use
5. Navigate to Expression Builder
Follow the steps below to navigate to the expression builder:
- Click Create Report.
- Select a Data Source.
- Click the + Add Values button.
- Click + Custom Values.
This sections below will help you understand the syntax and usage of various functions available for building expressions.
6. 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.
|
| Like – Matches string against SQL LIKE pattern (case-sensitive).
|
| ILike – Matches string against SQL LIKE pattern (case-insensitive).
|
7. 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.
|
8. Aggregate Expressions
Note:
- Expression fields should be included in row grouping. Owner Auto ID should be preferred for partitioning.
- Some of the expressions below support Partition. It divides a value or dataset into separate segments so each segment can be processed independently. This is useful when you want calculations or logic to run within specific groups rather than across the entire dataset.
| Function & Example Expression |
|
SUM The Sum function allows you to calculate the sum of numeric values within a given field. Example 1 – Create the calculated column which stores the sum of the field mx_Custom_2. We can use the Expression Builder to write the expression.
Example 2 – Calculates a running or total sum of Lead Score counts within each Owner’s partition.
|
| SumIf
The SumIf function allows you to calculate the sum of numeric values within a given field along with some conditions. Example 1 – 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.
Example 2 – Sums the count of Lead Score values only for rows where Lead Score is greater than 0, grouped by Owner.
|
| Count
The Count function lets you count the number of occurrences of a specific value or field within a given range. Example 1 – Create the calculated column which stores the count of the leads.
Example 2 – Counts the number of rows in each Team partition after computing the summed Lead Score for each row.
|
| CountIf
The CountIf function allows you to count the number of occurrences that meet a specific condition. Example 1 – Create a calculated column that stores the count of the leads when the lead stage is ‘Prospect’.
Example 2 – Counts how many leads have Status = Converted within each Owner partition.
|
| CountDistinct
The CountDistinct function allows you to count the unique number of occurrences that meet a specific condition. Example 1 – Create a calculated column that stores the count of unique leads.
Example 2 – Calculates the percentage contribution of distinct Prospect IDs relative to the overall total across all partitions (Used in a % Grand Total scenario).
% Grand Total Usecase |
| CountDistinctIf
The CountDistinctIf function allows you to count the distinct number of occurrences that meet a specific condition. Example 1 – Create a calculated column that stores the count of distinct leads when the lead stage is ‘Prospect’.
Example 2 – Counts distinct Prospect IDs where the Status is Converted, grouped by Owner.
|
| Max
The Max function allows you to calculate the maximum of numeric values within a given field. Example 1 – Create the calculated column which stores the maximum of the field mx_Custom_2.
Example 2 – Returns the maximum summed Lead Score within each Owner’s partition.
|
| Min
The Min function allows you to calculate the minimum of numeric values within a given field. Example 1 – Create the calculated column which stores the minimum of the field mx_Custom_2.
Example 2 – Returns the minimum summed Lead Score within each Owner’s partition.
|
| Avg
The Avg function allows you to calculate the average of numeric values within a given field. Example 1 – create the calculated column which stores the average of the field mx_Custom_2.
Example 2 – Returns the average summed Lead Score within each Owner’s partition.
|
| MaxIf
The MaxIf function allows you to calculate the maximum of numeric values within a given field along with some conditions. Example 1 – Create the calculated column which stores the maximum of the field mx_Custom_2 when Stage is ‘Prospect’.
Example 2 – Returns the maximum summed Lead Score but only for rows where Lead Score is greater than 0, grouped by Owner.
|
| MinIf
The MinIf function allows you to calculate the minimum of numeric values within a given field along with some conditions. Example 1 – Create the calculated column which stores the minimum of the field mx_Custom_2 when Stage is ‘Prospect’.
Example 2 – Returns the minimum count of Lead Score only for rows where Lead Score is greater than 0, grouped by Owner.
|
| AvgIf
The AVGIF function allows you to calculate the average of numeric values within a given field along with some conditions. Example 1 – Create the calculated column which stores the average of the field mx_Custom_2 when Stage is ‘Prospect’.
Example 2 – Returns the average count of Lead Score only for rows where Lead Score is greater than 0, grouped by Owner.
|
| ArgMax
Returns the value of target_field for which selector_field is maximum. For example, the expression below returns the Lead Source of the lead that has the most recent Updated On date.
|
| ArgMin
Returns the value of ‘target_field’ for which ‘selector_field’ is minimum. For example, the expression below returns the Lead Source of the lead that has the earliest Updated On date.
|
|
ArgMaxIf Returns the value of ‘target_field’ for which ‘selector_field’ is maximum where condition holds. For example, the expression below returns the Lead Source of the most recently updated lead, but only among leads where Status = Converted.
|
|
ArgMinIf Returns the value of ‘target_field’ for which ‘selector_field’ is minimum where condition holds. For example, the expression below returns the Lead Source of the earliest created lead, but only among leads where Status = New.
|
|
Any Returns any arbitrary value of the field from a group. For example, the expression below returns any one City value from the group.
|
|
AnyHeavy Returns the most frequent value (>50%) using heavy-hitters algorithm. For example, the expression below returns the most frequently occurring City, but only if it appears in more than 50% of the records.
|
|
AnyLast Returns the last-seen value based on processing order. For example, the expression below returns the last-seen Stage value based on the order in which records were processed.
|
9. 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
|
|
ToDayOfTheMonth Extracts day of month (1–31). For example, the expression below returns the day of the month (1–31) from the Created On date of the lead.
|
|
ToDayOfYear Extracts day number in year (1–366). For example, the expression below returns the day number in the year (1–366) extracted from the Audit Event Time field.
|
10. String and Column Functions
| Position
Returns 1-based index of substring; 0 if not found. For example, the expression below Checks whether the substring “Pro” exists in the Lead Stage field. If found (i.e., position > 0), it returns “This is Prospect”; otherwise, it returns “This is not Prospect”.
|
|
Concat Concatenates multiple fields into a single string. For example, the expression below combines the First Name and Last Name fields into a single full name string, inserting a space between them.
|
|
JSONExtractString Extracts string value from a JSON object by key. For example, the expression below extracts the “city” value from the JSON object stored in the AdditionalInfo field.
|
|
RegexpExtract Extracts regex capture group by index. For example, the expression below extracts the portion of the email before the ‘@’ symbol using a regular expression, and returns the first captured group (e.g., the username part of the email).
|
11. Window Functions
|
Lag Returns value from a previous row in partition. For example, the following expression returns the Lead Stage from the previous row within each Owner’s partition, based on the stage’s ascending order.
|
| Lead
Returns value from a following row in partition. For example, the following expression returns the Prospect Creation Date from the next row within each Owner’s partition, ordered by creation date in ascending order.
|
|
RowNumber Assigns sequential row number within partition. For example, the follwing expression assigns a sequential row number to each record within the Owner partition, ordered by Lead Stage.
|
|
FirstValue Returns first value in ordered partition. For example, the follwing expression returns the first Lead Stage value in each Owner’s partition, based on ascending stage order.
|
|
LastValue Returns last value in ordered partition. For example, the following expression returns the last Lead Stage value in each Owner’s partition, based on ascending stage order.
|
12. Custom Dimensions
| Function & Example Expressions |
| If
Returns a string based on a condition. The condition can be nested. Returned Value: (String) e.g., ‘Hot’, ‘Medium’, ‘Warm’, ‘Cold’ For example, let’s assume we want to classify leads based on their ProspectStage into ‘Hot’, ‘Medium’, ‘Warm’, or ‘Cold’ categories.
|
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!
Hi Team,
I am trying to build a report in SIERA (using the “All Activities” data source) and want to use a custom field for activities in the custom value expression. Specifically:
mx_Custom_1, display name “Reason”).Could you please confirm:
mx_Custom_1) in report expressions by default?Thanks for your help.
Hi. Yes, you can use custom activity fields, but you’ll need to email support@leadsquared.com to get this feature enabled. Please include the use case you’re trying to build, so the support team can assist you further.
I’m not able to create custom expression for Rows. Custom expression is not showing.
Hi. Custom expressions can only be applied to columns, not directly to row groupings.
To use an expression for rows, you first need to create a custom column (from the Data section). Once the custom column is created, you can then select it in your Row grouping or use it in your aggregations.
To learn more about custom columns, refer to Create Custom Columns in SIERA.
I need Help, I am creating a report, In rows, I have partner names, in values, I have Number of leads from respective partners are coming. Now, I need one more column, I which I want to show % of leads from respective partners. How can I get sum of all the leads from all the partners.
1st column I am getting correctly, but for second one, I don’t know How to get the total opportunity value from summary.
Hi. At present, this feature isn’t supported on SIERA, but should be available in a future release.
Hey, is there a ‘like’ function in SIERRA? Like how it works in SQL
Eg: first_name like ‘%raj%’ so it will pull all first_names that have raj
Hi. At present, this functionality isn’t available. But I’ve passed this on to the product team as a feature request.
Hi, I’m trying to figure out how if statements work in SIERRA?
Basically I have two columns connected disposition and not connected disposition and I want to combine both so I want to write an IF statement – if connected is null then not connected else connected – basically an ifnull. But it does not seem to work.
Ref: If(
Activity.mx_Custom_1= Null,Activity.mx_Custom_2,Activity.mx_Custom_1)Hi Ishan. You might need to replace
Nullin the expression with an empty string (''). Could you try the expression below and let me know if it works?If(Activity.mx_Custom_1 = '', Activity.mx_Custom_2, Activity.mx_Custom_1)If I want to count on basis of data contains specific character, then how to use it SIERA.
For Example: Column1 has following data
A123
12C4
H543
8890
GHS0
9430
I want to count if data contains “4″ then output will be 3 (i.e. 12C4, H543, 9430)
How to do this in SIERA
Hi, Ajay. At present, you can’t execute such a query. But I’ve communicated this as a feature request to the team.
Is there any function like starts with specific character
For Example: Column1 has following data
A123
12C4
H543
8890
A783
GHS0
9430
I want count of output where data startswith “A” i.e. A123, A783.
Hi, Ajay. As mentioned before, this functionality isn’t available yet.
Is there any alternative of this functions so we can use it to create Dashboards
There aren’t any alternatives available at the moment.