Formulas
SmartIQ can parse formulas made up of functions to perform various dynamic formatting tasks or calculations.
Functions
Functions require a set of parameters to determine a result. These parameters may be literal values, references to other questions, or even other functions. Functions must include the function name followed by any parameters in brackets, or empty brackets if no parameters are required.
To perform more complex calculations, formulas may also incorporate various math, date and text functions. For example, the DateDiff() function can be used to calculate a person’s age based on their date of birth and today’s date.
Best Practice
Parameters are separated by commas and for best practice spaces should be inserted after commas to avoid issues for users using international cultures.
Best Practice
Formulas in Variable questions can reference other SmartIQ questions. Whenever possible, questions should only refer to other questions which come earlier in the form structure. References to later questions (often called a "forward reference") may work but can also behave unreliably; for this reason forward references are not recommended.
Functions executed on datasource data will cause SmartIQ to attempt to load the entire datasource
This is not tractable for data sets larger than SmartIQ's data retrieval limitation, prefiltering of the datasource either from within or using filters on unmodified fields is advised.
Aggregate functions
Aggregate functions are a specific subset of function, that requires multiple values for the computation to make sense, for example calculating an average(). These functions require a reference to a multi-row Data Question, or a question in a Repeating Page or Section which may be of any question type. Aggregate functions examine all the values retrieved to determine a result, and can be identified in the functions lists as they ask for a question reference to a repeater or data-source.
Aggregate functions can handle multiple directly input individual values, for example average([q1.income], [q17.income]) will average q1 and q17 incomes.
Inputs can also be defined through strings by placing pipe characters '|' between the input values. The aggregate function can then pass the individual variables that string. For example a user types '1|2|3' into a text box that is referenced 'average([textbox])', the average will be calculated on values 1, 2 and 3.
Defaulting a Function output
Formulas can be used in any text box within question properties for dynamic calculation, however if the function/s used don't have a valid input the formula will display in the location where the output is meant to display. This issue can be solved by setting a defaults for the function inputs using Variable questions and Reference Tags.
Auto formats strings syntax
There are a number of strings of symbols or terms that can be used to automatically format text.
Answer Formatting
| Format | Description |
|---|---|
| Percent | Display number multiplied by 100 with a percent sign (%) appended to the right; always display two digits to the right of the decimal separator. |
| Currency | Display number with a thousand separator, if appropriate; display two digits to the right of the decimal separator. The output is based on system locale settings. |
| General Number | Display number with no thousand separator |
| Uppercase | Displays all text in upper case. |
| Lowercase | Displays all text in lower case. |
| Uppercase first letter | Displays the first letter of text in upper case, with the rest in lower case. |
| Propercase | Capitalize the first character of each word. |
| Number to text | Displays the number as text rather than a figure, for example, 100 would display as ‘One Hundred’. |
| Number to text uppercase | Displays number as uppercase text |
| Number to text lowercase | Displays number as lowercase text |
| Currency to text | Two of these options exists one ... |
| Currency to cheque text | |
| Left Pad | |
| Right Pad | |
| Short Date | |
| Long Date | |
| Date to Epoch in Milliseconds | TO EPOCH MS: The ToEpochMS formula converts a given date to an epoch format in milliseconds. |
| Epoch Milliseconds to UTC Date | FROM EPOCH MS: The From Epoch MS formula converts an epoch value in milliseconds to a universal date. |
| Left number of characters | Displays the first left character |
| Right number of characters | Displays the first right character |
| Trim start to end spaces | Removes all spaces from the answer string |
| Relative Time | Displays the users current relative time |
Named Numeric Formats
When specifying common formats for numbers there are several named terms can be used. For example 'currency' could be used rather than '$0.00'. The terms are useful for the format function or document format property.
| Format | Description |
|---|---|
| Percent | Display number multiplied by 100 with a percent sign (%) appended to the right; always display two digits to the right of the decimal separator. |
| Currency | Display number with a thousand separator, if appropriate; display two digits to the right of the decimal separator. The output is based on system locale settings. |
| General Number | Display number with no thousand separator |
| Fixed | Display at least one digit to the left and two digits to the right of the decimal separator. |
| Standard | Display number with a thousand separator, at least one digit to the left and two digits to the right of the decimal separator. |
| Scientific | Use standard scientific notation. |
| Yes/No | Display ‘No’ if the number is 0; otherwise, display ‘Yes’. |
| True/False | Display ’False’ if the number is 0; otherwise, display ‘True’. |
| On/Off | Display ‘Off’ if the number is 0; otherwise, display On’. |
Custom date/time format strings
This page is a summary of the information from the Custom date and time format strings from Microsoft.
For detailed information and examples on the use of these formats please see the Microsoft page.
| Format | Description |
|---|---|
| [:] | Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system's LocaleID value. |
| [/] | Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your locale. |
| [%] | Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a user-defined format. See below for further details. |
| d | Displays the day as a number without a leading zero (for example, 1). Use %d if this is the only character in your user-defined numeric format. |
| dd | Displays the day as a number with a leading zero (for example, 01). |
| ddd | Displays the day as an abbreviation (for example, Sun). |
| dddd | Displays the day as a full name (for example, Sunday). |
| M | Displays the month as a number without a leading zero (for example, January is represented as 1). Use %M if this is the only character in your user-defined numeric format. |
| MM | Displays the month as a number with a leading zero (for example, 01/12/01). |
| MMM | Displays the month as an abbreviation (for example, Jan). |
| MMMM | Displays the month as a full month name (for example, January). |
| gg | Displays the period/era string (for example, A.D.) |
| h | Displays the hour as a number without leading zeros using the 12-hour clock (for example, 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format. |
| hh | Displays the hour as a number with leading zeros using the 12-hour clock (for example, 01:15:15 PM). |
| H | Displays the hour as a number without leading zeros using the 24-hour clock (for example, 1:15:15). Use %H if this is the only character in your user-defined numeric format. |
| HH | Displays the hour as a number with leading zeros using the 24-hour clock (for example, 01:15:15). |
| m | Displays the minute as a number without leading zeros (for example, 12:1:15). Use %m if this is the only character in your user-defined numeric format. |
| mm | Displays the minute as a number with leading zeros (for example, 12:01:15). |
| s | Displays the second as a number without leading zeros (for example, 12:15:5). Use %s if this is the only character in your user-defined numeric format. |
| ss | Displays the second as a number with leading zeros (for example, 12:15:05). |
| F | Displays fractions of seconds. For example ff will display hundredths of seconds, whereas ffff will display ten-thousandths of seconds. You may use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format. |
| T | Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format. |
| tt | Uses the 12-hour clock and displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. |
| y | Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format. |
| yy | Displays the year in two-digit numeric format with a leading zero, if applicable. |
| yyy | Displays the year in four digit numeric format. |
| yyyy | Displays the year in four digit numeric format. |
| z | Displays the timezone offset without a leading zero (for example, -8). Use %z if this is the only character in your user-defined numeric format. |
| zz | Displays the timezone offset with a leading zero (for example, -08) |
| zzz | Displays the full timezone offset (for example, -08:00) |
Special Document Format Strings
The following are format strings available for use in the “Document Format” field of Answers. The first four formats (“+”, “-“, “+-“ and “+-+-“) are additionally available for use within the Format() function. All other format strings listed here are available only within the Document Format field.
| Format String | Description |
|---|---|
| + | Displays all text in upper case. |
| - | Displays all text in lower case. |
| +- | Displays the first letter of text in upper case, with the rest in lower case. |
| +-+- | Capitalize each word, title case. |
| one | Displays the number as text rather than a figure, for example, 100 would display as ‘One Hundred’. |
| +one | Displays a number as uppercase text. |
| one | Displays a number as lower case text. |
| +-one | Displays a numer in text that starts with a capital letter with the rest in lower case. |
| lpad | Pads the left of the value with a character to the specified length, for example, LPAD '0'5 would return 00001 for a value of 1. |
| rpad | Pads the right of the value with a character to the specified length, for example, RPAD 'A' 10 would return HelloAAAAA for a value of Hello. |
| Left n | Provides the first n amount of letters in a text string. |
| Right n | Provides the last n amount of letters in a text string. |
| -Left n | Provides the first n amount of letters in a text string in lower case. |
| -Right n | Provides the last n amount of letters in a text string in lower case. |
| +Left n | Provides the first n amount of letters in a text string in upper case. |
| +Right n | Provides the last n amount of letters in a text string in upper case. |
| Trim | Removes any leading or trailing spaces. |
| OADate | Format string for converting serial dates (OLE Automation or OA Dates) into actual dates. e.g. OADate {‘f’}, where ‘f’ is an optional inner format string for altering the format of the date produced by the OA date conversion. e.g.2 OADate ‘dd MMM yyyy’ The OA date format is used when calculating an end date based on a start date and a number of days to add. To add the number of days from [q2] user prompt to a date entered in user prompt [q1] use the below formula with an [OADate] format. errormask(DateDiff(4, #30-dec-1899, [q1]), 0) + errormask([q2], 0) |
Custom numeric format strings
You can create a custom numeric format string, which consists of one or more custom numeric specifiers, to define how to format numeric data. A custom numeric format string is any format string that is not a standard numeric format string.
The following table describes the custom numeric format specifiers and displays sample output produced by each format specifier.
Commas, decimal points, percent symbols and zeros are all specific format specifiers. Using these symbols in combination with the # digit placeholder to create format patterns may cause in unexpected results. If any of these symbols are used as character literals rather than format specifiers they must be escaped with the \ escape character. See below for more information.
This information is a summary of the Custom numeric format strings page from Microsoft and includes links to more information on each Format string from that page.
See the Notes section for additional information about using custom numeric format strings, and the Example section for a comprehensive illustration of their use.
| Format String | Name | Description | Examples |
|---|---|---|---|
| 0 | Zero placeholder | Replaces the zero with the corresponding digit if one is present; otherwise, zero appears in the result string. More information: The "0" Custom Specifier. | 1234.5678 ("00000") -> 01235 Culture: en-US 0.45678 ("0.00") -> 0.46 Culture: fr-FR 0.45678 ("0.00") -> 0,46 |
| # | Digit placeholder | Replaces the "#" symbol with the corresponding digit if one is present; otherwise, no digit appears in the result string. Note that no digit appears in the result string if the corresponding digit in the input string is a non-significant 0. For example, 0003 ("####") -> 3. More information: The "#" Custom Specifier. | 1234.5678 ("#####") -> 1235 Culture: en-US 0.45678 ("#.##") -> .46 Culture: fr-FR 0.45678 ("#.##") -> ,46 |
| . | Decimal point | Determines the location of the decimal separator in the result string. More information: The "." Custom Specifier. | Culture: en-US 0.45678 ("0.00") -> 0.46 Culture: fr-FR 0.45678 ("0.00"FR) -> 0,46 |
| , | Group separator and number scaling | Serves as both a group separator and a number scaling specifier. As a group separator, it inserts a localized group separator character between each group. As a number scaling specifier, it divides a number by 1000 for each comma specified. More information: The "," Custom Specifier. | Group separator specifier: Culture: en-US 2147483647 ("##,#") -> 2,147,483,647 Culture: es-ES 2147483647 ("##,#") -> 2.147.483.647 Scaling specifier: Culture: en-US 2147483647 ("#,#,,") -> 2,147 Culture: es-ES 2147483647 ("#,#,,") -> 2.147 |
| % | Percentage placeholder | Multiplies a number by 100 and inserts a localized percentage symbol in the result string. More information: The "%" Custom Specifier. | Culture: en-US 0.3697 ("%#0.00") -> %36.97 Culture: el-GR) 0.3697 ("%#0.00") -> %36,97 Culture: en-US 0.3697 ("##.0 %") -> 37.0 % Culture: el-GR) 0.3697 ("##.0 %") -> 37,0 % |
| ‰ | Per mille placeholder | Multiplies a number by 1000 and inserts a localized per mille symbol in the result string. More information: The "‰" Custom Specifier. | Culture: en-US 0.03697 ("#0.00‰") -> 36.97‰ Culture: ru-RU 0.03697 ("#0.00‰") -> 36,97‰ |
| E0 E+0 E-0 e0 e+0 e-0 | Exponential notation | If followed by at least one 0 (zero), formats the result using exponential notation. The case of "E" or "e" indicates the case of the exponent symbol in the result string. The number of zeros following the "E" or "e" character determines the minimum number of digits in the exponent. A plus sign (+) indicates that a sign character always precedes the exponent. A minus sign (-) indicates that a sign character precedes only negative exponents. More information: The "E" and "e" Custom Specifiers. | 987654 ("#0.0e0") -> 98.8e4 1503.92311 ("0.0##e+00") -> 1.504e+03 1.8901385E-16 ("0.0e+00") -> 1.9e-16 |
| \ | Escape character | Causes the next character to be interpreted as a literal rather than as a custom format specifier. More information: The "" Escape Character. | 987654 ("###00#") -> #987654# |
| 'string' "string" | Literal string delimiter | Indicates that the enclosed characters should be copied to the result string unchanged. More information: Character literals. | 68 ("# 'degrees'") -> 68 degrees 68 ("#' degrees'") -> 68 degrees |
| ; | Section separator | Defines sections with separate format strings for positive, negative, and zero numbers. More information: The ";" Section Separator. | 12.345 ("#0.0#;(#0.0#);-\0-") -> 12.35 0 ("#0.0#;(#0.0#);-\0-") -> -0- -12.345 ("#0.0#;(#0.0#);-\0-") -> (12.35) 12.345 ("#0.0#;(#0.0#)") -> 12.35 0 ("#0.0#;(#0.0#)") -> 0.0 -12.345 ("#0.0#;(#0.0#)") -> (12.35) |
| Other | All other characters | The character is copied to the result string unchanged. More information: Character literals. | 68 ("# °") -> 68 ° |
Updated 3 months ago