Formula Fields in Data Designer

Date Formulas

The following is the list of Date Formulas supported in Data Designer:

• Add / Subtract Date: You can use this function to perform addition and subtraction operations on a date. These operations can be performed on a date used in the dataset. You can add or subtract any number of days or weeks to a date.
• Date: Converts Date-time argument to Date.
• First Day of Calendar Quarter: Returns the first day of the Calendar Quarter.
• First Day of Current Month: Returns the first day of Current Month.
• First Day of Current Week: Returns the first day of the Current Week.
• Last Day of Calendar Quarter: Returns the last day of the Calendar Quarter. For example, the last day of the calendar quarter (3/27/2019) will return "3/31/2019" as the output.
• Last Day of Current Month: Returns the last day of Current Month.
• Last Day of Current Week: Returns the last day of Current Week. For example, the last day of the week (6/2/2022) will return "5/29/2022" as the output.
• Run Date: The Run Date function does not accept any arguments. It only returns the Current Date on which the design is being executed.
• Epoch to Date(Field, default value(yyyy-mm-dd)): Returns the epoch number for the selected field in the form of date.
• To Date: This parameter returns any string data type field or value containing date into the Date format provided by the user.
  Note: If the date format is not provided, the default value considered is yyyy-MM-dd.

Note: The international standard ISO 8601 for date and time representation states that Sunday is the seventh and last day of the week.

String Formulas

The following is the list of String Formulas supported by Data Designer:

• Concat: Concatenates the selected arguments. A maximum of 10 arguments can be added and one string can be used multiple times.
• Substring(Field, Position, Length): Finds the substring based on the position and length. For example, Substring(“johndoe”, 2, 3) returns “ohn”.
• To Lower Case(Source Field): Converts the selected argument values to Lowercase.
• To Upper Case(Source Field): Converts the selected argument values to Uppercase.
• Left Trim( Field, String to trim): Trims the left part of the string from the appearance of the string character to trim. For example, Left Trim (Johndoe@gainsight.com,J) will return  ohndoe@gainsight.com as output. Trims left hand trailing spaces by default if no input is provided.
• Right Trim(Field, string to trim): Trims the right part of the string from the appearance of the string character to trim. For example, Right Trim (Johndoe@gainsight.com,om) will return Johndoe@gainsight.c as output. Trims right hand trailing spaces by default if no input is provided.
• Full Trim(Field, string to trim): Trims the left and right part of the string from the appearance of the string character to trim. For example, Full Trim (Johndoe@gainsight.com, white spaces) will return Johndoe@gainsight as output with white spaces removed from both ends of the string.
• To String(Field, value): Converts the selected argument values to string.

Number Formulas

The following is the list of Number Formulas supported by Data Designer:

• ABS: The Abs formula returns the absolute value of a number in the selected field.
• Ciel: The Ceiling formula maps a number to the least integer greater than or equal to the number.
• Correlation: The Correlation formula  returns the correlation coefficient, which can be used to determine the relationship between two fields such as ARR and Churn ARR.
• Covariance: The Covariance formula  returns the average of deviations for each data point pair in two data sets.
• Date Diff: The Date Diff returns the difference between the two selected dates in the selected unit.
• Exp: The Exp formula returns Euler's number, e (~2.718) raised to a power.
  Example:
                EXP(1) = Approximate value of e = 2.71828183
                EXP(2) = Base of the natural logarithm e raised to the power of 2 = 7.3890561
• Floor: The Floor formula maps a number to the greatest integer less than or equal to the number.
• Length: The Length formula provides the length of the string. For example, LENGTH(“string”) returns 6.
• In: The ln formula returns the natural logarithm of a number, base e (Euler's number).
• Log: The Log formula returns the logarithm of a number given a base.
• Median: The Median formula returns the median of the field selected. The median is the number in the middle of a set of numbers.
• Position: The Position formula provides the position of the substring in a string. For example, POSITION(“doe” in “johndoe”) returns 5. Counting of the position starts with an index 1.
• Sqrt: The Sqrt formula returns the positive square root of a positive number.
• Standard Deviation: Both Standard Deviation and Variance measure volatility / variability. A high value indicates that the dataset is spread out; a low standard deviation means that the dataset values are close to the mean.
• Variance:  Variance measures how far a set of numbers are spread out from their average value.
• To Number(Field, Default value): Converts the selected field to number.

Boolean Formulas

The following is the list of Boolean Formulas supported by Data Designer:

• Is Empty(Field): This parameter returns True if the input data is empty / Null and returns False if the data is filled.
• To Boolean(Field, Default value): This parameter returns True if the input data in the field contains a number and returns False if the input data does not contain a number.

DateTime Formulas

The following is the list of DateTime Formulas supported by Data Designer:

• Epoch to DateTime(Field, default value): Returns the epoch number for the selected field in the form of date and time.
• To DateTime(Field, format of datetime, default value): This parameter returns the value of the field selected in the Date-Time format provided by the user.
  Note: If the date-time format is not provided, the default value considered is yyyy-mm-dd HH:mm:ss.