HomeGuidesRecipesAPI
HomeGuidesAPILog In

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

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.

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.

FormatDescription
General NumberDisplay number with no thousand separator
CurrencyDisplay 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.
FixedDisplay at least one digit to the left and two digits to the right of the decimal separator.
StandardDisplay number with a thousand separator, at least one digit to the left and two digits to the right of the decimal separator.
PercentDisplay number multiplied by 100 with a percent sign (%) appended to the right; always display two digits to the right of the decimal separator.
ScientificUse standard scientific notation.
Yes/NoDisplay ‘No’ if the number is 0; otherwise, display ‘Yes’.
True/FalseDisplay ’False’ if the number is 0; otherwise, display ‘True’.
On/OffDisplay ‘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.

FormatDescription
[:]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.
dDisplays 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.
ddDisplays the day as a number with a leading zero (for example, 01).
dddDisplays the day as an abbreviation (for example, Sun).
ddddDisplays the day as a full name (for example, Sunday).
MDisplays 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.
MMDisplays the month as a number with a leading zero (for example, 01/12/01).
MMMDisplays the month as an abbreviation (for example, Jan).
MMMMDisplays the month as a full month name (for example, January).
ggDisplays the period/era string (for example, A.D.)
hDisplays 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.
hhDisplays the hour as a number with leading zeros using the 12-hour clock (for example, 01:15:15 PM).
HDisplays 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.
HHDisplays the hour as a number with leading zeros using the 24-hour clock (for example, 01:15:15).
mDisplays 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.
mmDisplays the minute as a number with leading zeros (for example, 12:01:15).
sDisplays 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.
ssDisplays the second as a number with leading zeros (for example, 12:15:05).
FDisplays 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.
TUses 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.
ttUses 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.
yDisplays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
yyDisplays the year in two-digit numeric format with a leading zero, if applicable.
yyyDisplays the year in four digit numeric format.
yyyyDisplays the year in four digit numeric format.
zDisplays the timezone offset without a leading zero (for example, -8). Use %z if this is the only character in your user-defined numeric format.
zzDisplays the timezone offset with a leading zero (for example, -08)
zzzDisplays the full timezone offset (for example, -08:00)

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 StringNameDescriptionExamples
0Zero placeholderReplaces 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 placeholderReplaces 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 pointDetermines 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 scalingServes 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 placeholderMultiplies 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 placeholderMultiplies 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 notationIf 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 characterCauses 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 delimiterIndicates 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 separatorDefines 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)
OtherAll other charactersThe character is copied to the result string unchanged.

More information: Character literals.
68 ("# °") -> 68 °