SIERA Expression Builder

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>) <= 1925 will 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. Types of Expressions

Expressions in SIERA are of 3 types –

  • Aggregated ExpressionsAn 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>)
  • Conditional ExpressionsConditional 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>).
  • Calculated ValuesCalculated 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. 

 

5. Expression Builder Syntax

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.

expression builder nav

 

6. Operators, Conditions, and Constants

Function & Example Expression
Equal​ Used to compare if 2 values or fields are equal.

`Lead.ProspectStag` = 'Customer'

Not Equal​ Used to compare if 2 values or fields are not equal.

`Lead.ProspectStage` != 'Customer'

Less Than​ Used to compare if a value or field is less than compared to another value or field.

`Lead.Score` < 50

Less Than or Equal​ Used to compare if a value or field is less than or equal to another value or field.

`Lead.Score` <= 50

Greater Than – Used to compare if a value or field is greater than compared to another value or field.

`Lead.Score` > 50

Greater Than or Equal ​Used to compare if a value or field is greater than or equal to another value or field. 

`Lead.Score` >= 50

in – Used to determine whether the value exists in a list of values.

`Lead.ProspectStage` in ['INVALID', 'DISQUALIFIED']

notin – Used to determine whether the value does not exist in a list of values.

`Lead.ProspectStage` notin ['INVALID', 'DISQUALIFIED']

Text Constant Text constant to be written as ‘Text’, with quotations.

`Lead.ProspectStage` notin ['INVALID', 'DISQUALIFIED']

Number Constant Text constant to be written as 50, without quotations.

Lead.Score1 >= 50

Addition Used to add two values in your report. Fields (Number type) and constants can be used as operands

Sum(`Lead.mx_custom2`) + Sum(`Lead.mx_custom3`)

Subtraction Used to subtract two values in your report. Fields (Number type) and constants can be used as operands. 

Sum(`Lead.mx_custom2`) - Sum(`Lead.mx_custom3`)

Multiplication Used to multiply two values in your report. Fields (Number type) and constants can be used as operands. 

(Sum(`Lead.mx_custom2`) + Sum(`Lead.mx_custom3`)) * 100

Division Used to divide two values in your report. Fields (Number type) and constants can be used as operands. 

(Sum(`Lead.mx_custom2`) + Sum(`Lead.mx_custom3`)) / 100

 

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.

(<expression1>) and (<expression2>)

(`Lead.ProspectStage` = 'Customer') and (`Lead.Source` in ['Referral','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.

(<expression1>) or (<expression2>)

(`Lead.ProspectStage` = 'Customer') or (`Lead.mx_Sub_Stage` in ['Converted','Paid'])

 

8. 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.

Sum(<fieldname>)

Sum(`Lead.mx_Custom_2`)

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.

SumIf(<fieldname>,<condition>)

SumIf(`Lead.mx_Custom_2`, Stage = 'Customer')

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.

Count(`Lead.ProspectId`)

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’.

CountIf(`Lead.ProspectStage` = '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.

CountDistinct(`Lead.ProspectId`)

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’.

CountDistinctIf(`Lead.ProspectId`, `Lead.ProspectStage` = '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.

Max(<fieldname>)

Max(`Lead.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.

Min(<fieldname>)

Min(`Lead.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. 

Avg(<fieldname>)

Avg(`Lead.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’.

MaxIf(<fieldname>,<condition>)

MaxIf(`Lead.mx_Custom_2`, `Lead.ProspectStage` = '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’.

MinIf(<fieldname>,<condition>)

MinIf(`Lead.mx_Custom_2`, `Lead.ProspectStage` = '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’. 

AvgIf(<fieldname>,<condition>)

AvgIf(`Lead.mx_Custom_2`, `Lead.ProspectStage` = 'Prospect')

 

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’.

DurationAdd(<date>,<value>,<Unit>)

CountIf(`Lead.ProspectStage` != 'Customer' and DurationAdd(`Lead.mx_Custom_2`,4, 'MONTH') < CurrentDateTime)

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’.

DurationSubtract(<date>,<value>,<Unit>)

CountIf(`Lead.ProspectStage` != 'Customer' and `Lead.mx_Custom_2` < DurationSubtract(CURRENTDATETIME, 4, 'MONTH'))

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.

CurrentDateTime

DurationSubtract(CurrentDateTime, 4, 'MONTH'))

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.

ToHour(<date>)

CountIf(ToHour(`Lead.CreatedOn`)='17:00')

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.

ToYear(<date>)

ToYear(`Lead.mx_Custom_2`)= 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.

ToYearWeek(<date>)

ToYearWeek(`Lead.mx_Custom_2`)=47

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.

ToMonthOfYear(<date>)

ToMonthOfYear(`Lead.mx_Custom_2`)='0002-02-01'

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.

ToDateOfYear(<date>)

ToDateOfYear(`Lead.mx_Custom_2`)='2023-02-02'

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.

ToQuarterOfYear(<date>)

ToQuarterOfYear(`Lead.mx_Custom_2`)='Q2'

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.

ToMonth(<date>)

CountIf( ToMonth(`Lead.CreatedOn`) = '2023-02-01' )

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.

​​​​​ToQuarter(<date>)

CountIf( ToQuarter('Lead.CreatedOn') = '2023 Q2' )

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.

ToDayOfWeek(<date>)

CountIf( ToDayOfWeek(`Lead.CreatedOn`) = 6 )

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.

ToWeek(<date>)

CountIf( ToWeek(`Lead.CreatedOn`) = 202347 )

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

DateUnitDiff(<start date>, <end date>, <interval>)

Accepted Intervals – 

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR


For example, consider your data source has 2 fields – CreatedOn and PaymentDate which capture the lead created and the payment date of the lead. To create a TAT report (in days) on the PaymentDate from the lead created, we can use the expression below –
Avg(DateUnitDiff(`Lead.CreatedOn`, `Lead.mx_Payment_Date`, 'DAY'))

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-

AvgIf(DateUnitDiff(`Lead.CreatedOn`, `Lead.mx_Payment_Date`, 'DAY'),ToYear(`Lead.mx_Payment_Date`) > 1925)

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

ToDate(<date>)

CountIf(ToDate(`Lead.Createdon`) = '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!

Was this Helpful?

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments