<trial ChatGpt> Function Calls

Designer Administrator
This manual is in pilot operation.

In expressions, you can call built-in functions. Functions take multiple arguments (though some functions do not require any arguments) and return a single value. You can perform further calculations or conditional judgments based on the returned value.
The functions available vary depending on the type of expression.

Function names start with @.
  @some_function (1)
1 Calls the function named some_function without any arguments. Writing @some_function() is equivalent.
Arguments are passed enclosed in (). If there are multiple arguments, separate them with ,.
  @other_function(1, "a") (1)
1 Calls the function named other_function with the arguments 1 and a.

The functions available are as follows:

Functions Available in All Dialects

Numeric Calculation Functions

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@abs
(fusion_place >= 10.0)

First argument

Number

Returns the absolute value of a number.

If the argument is not a number, it returns the argument as is.

@max
(fusion_place >= 10.0)

Any number of arguments

Returns the largest number among the arguments.

If there are no arguments, or if none of the arguments are numbers, it returns 0.

@min
(fusion_place >= 10.0)

Any number of arguments

Returns the smallest number among the arguments.

If there are no arguments, or if none of the arguments are numbers, it returns 0.

@round

First argument

Number

Second argument

Number: Number of digits

Rounds a number to the nearest integer. Rounding towards zero is not towards negative infinity but towards zero, and rounding up is towards infinity if the number is positive, and towards negative infinity if the number is negative. For example, rounding -4.5 to the nearest whole number results in -5, not -4.

The specification for the number of digits is as follows:

  1. To keep only the integer part (round to the nearest whole number) - the number of digits is 0

  2. To keep up to the first decimal place (round the second decimal place) - the number of digits is 1

  3. To keep up to the second decimal place (round the third decimal place) - the number of digits is 2

  4. To keep tens and higher (round the ones place) - the number of digits is -1

  5. To keep hundreds and higher (round the tens place) - the number of digits is -2

The number of digits can be an integer between -14 and 4.

@round_up

(Same as @round)

Rounds a number up to the nearest integer. Rounding up is towards infinity if the number is positive, and towards negative infinity if the number is negative.
The specification for the number of digits is the same as for @round.

@round_down

(Same as @round)

Rounds a number down to the nearest integer. Rounding down is towards zero regardless of the number’s sign.
The specification for the number of digits is the same as for @round.

@multiply_fraction

First argument

Number: Multiplier

Second argument

Number: Numerator of the multiplicand

Third argument

Number: Denominator of the multiplicand

Calculates a * b / c without rounding or overflow during the calculation. The result is rounded to the nearest fifth decimal place, yielding a result accurate to four decimal places.

The first to third arguments correspond to the above a to c, respectively.

String Processing Functions

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@is_numeric

First argument

String

Determines if the string can be interpreted as a number. Returns "True" if possible, otherwise "False".

@numeric

First argument

String

Converts a string to a number.

@left

First argument

String

Second argument

Number of characters

Extracts the left n characters from a string.

If the specified number of characters is negative, it extracts from the left of the string, leaving (string length + number of characters). For example, if the string length is 5 and the number of characters is -2, it extracts the first 3 characters from the left (leaving 2 characters from the right).

@right

(Same as above)

Extracts the right n characters from a string.

If the specified number of characters is negative, it extracts from the right of the string, leaving (string length + number of characters). For example, if the string length is 5 and the number of characters is -2, it extracts the last 3 characters from the right (leaving 2 characters from the left).

@mid

First argument

String

Second argument

Number: Starting position

Third argument

Number of characters

Extracts a substring from a string starting at the specified position. The starting position begins at 1.

@trim
(fusion_place >= 10.0)

First argument

String

Returns a string with leading and trailing whitespace removed.

@len

First argument

String

Returns the length of a string.

@text

First argument

Number

Second argument

Number: Format pattern

[Third argument]

Number: Place value

Returns a string formatted according to the specified format pattern for a number.

The format pattern follows the same notation as used in the form’s methods of displaying amounts and numbers.

The place value specifies the power of 10 for the formatting (assumed to be 0 if not specified): 0 for as-is, 1 for tens, 3 for thousands, etc.

@repeat

First argument

String

Second argument

Number: Number of repetitions

Returns a string that repeats the specified string the specified number of times. Returns an empty string if the second argument is zero or less.

@find
(fusion_place >= 10.0)

First argument

String: Search string

Second argument

String: Target string

[Third argument]

Number: Starting position

Returns the first position at which the search string appears in the target string. Case-sensitive.

If an empty string is specified as the search string, it is considered a match immediately after the starting position, and the starting position is returned.

The starting position specifies where to begin the search (starting at 1). If omitted, it is assumed to be 1.

The return value is the position relative to the beginning, regardless of the starting position. If the target string is not found, the error value #VALUE! is returned.

@substitute

First argument

String

Second argument

String: String to be replaced

Third argument

String: Replacement string

Replaces all occurrences of a specific substring within a string.

@split

First argument

String

Second argument

String: Delimiter

Third argument

Number: Index

Returns the substring at the specified index when the string is split by the delimiter. Index starts at 1.

@num_of_chars_of_type

First argument

Number: Integer representing the type of character

Second argument

String: String to inspect

Counts how many characters of the specified type are in the string and returns the count.

The first argument is an integer representing the type of character. Specify one of the following or their sum:

1

Digit

2

Uppercase letter

4

Lowercase letter

8

Whitespace

If a sum is specified, it returns the total count of characters belonging to any of the specified types.

The second argument specifies the string to inspect.

For example, to determine if the cell specified by [PRO_CD] contains only alphanumeric characters, do the following:

  @num_of_chars_of_type(1+2+4, [PRO_CD].Text)=@len([PRO_CD].Text)

@num_of_listed_chars

First argument

String: String listing characters to search for

Second argument

String: String to inspect

Counts how many of the specified characters are in the string and returns the count.

The first argument is a string listing the characters to inspect. Note that including the same character multiple times counts it multiple times.

The second argument specifies the string to inspect.

For example, to count the number of - and + characters in the string value of the cell specified by [PRO_CD], do the following:

  @num_of_listed_chars("-+", [PRO_CD].Text)

@asc
(fusion_place >= 12.0)

First argument

String: String to convert

Converts full-width characters within the string to half-width characters. Applies to full-width alphanumeric symbols and katakana as per the full-width to half-width correspondence table.

The first argument is the string to convert.

For example, to convert full-width characters in the string value of the cell specified by [PRO_CD] to half-width characters, do the following:

  @asc([PRO_CD].Text)

@jis
(fusion_place >= 12.0)

First argument

String: String to convert

Converts half-width characters within the string to full-width characters. Applies to half-width alphanumeric symbols and katakana as per the half-width to full-width correspondence table.

The first argument is the string to convert.

For example, to convert half-width characters in the string value of the cell specified by [PRO_CD] to full-width characters, do the following:

  @jis([PRO_CD].Text)

Date Calculation Functions

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@date

First argument

String or Number: Gregorian year

Second argument

String or Number: Month number

Third argument

String or Number: Day

[Fourth argument]

String or Number: Month offset

Returns a date serial number (integer value). The date serial number is compatible with Excel’s date serial number[1]. The value given to this function’s arguments can be either a number (e.g., 2020) or a string (e.g., "2020").

By combining the month number and month offset, it’s possible to easily calculate the date serial number for fiscal years different from the calendar year.

Example) For a fiscal year ending in March, the date serial number for the 4th month of the fiscal year 2020 (i.e., 2020/7/1)

  @date(2020,4,1,3)

For a fiscal year ending in March, the fiscal year-end month shifts forward by 3 months, hence the month offset = 3.

Example) For a fiscal year ending in June, the date serial number for the 4th month of the fiscal year 2021 (i.e., 2020/10/1)

  @date(2020,4,1,-6)

For a fiscal year ending in June, the fiscal year-end month shifts backward by 6 months, hence the month offset = -6.

If not specified, the month offset is considered to be 0.

If there’s an error with the Gregorian year, month number, day, or month offset, or if their combination results in an incorrect date, the error value "#VALUE" is returned.


1. However, since Excel incorrectly calculates the date serial number by treating 1900 as a leap year, values before 1900/2/28 are not compatible with this function.

@year

First argument

Number: Date serial number

Returns the year of the date represented by the date serial number.

@month

First argument

Number: Date serial number

Returns the month of the date represented by the date serial number.

@day

First argument

Number: Date serial number

Returns the day of the month of the date represented by the date serial number.

@day_of_week

First argument

Number: Date serial number

Returns the day of the week of the date represented by the date serial number.

1

Monday

2

Tuesday

3

Wednesday

4

Thursday

5

Friday

6

Saturday

7

Sunday

@last_day_of_month

First argument

Number: Date serial number

Returns the date serial number of the last day of the month for the date represented by the date serial number (considering leap years).

@week_anchor

First argument

Number: Start date of the attribution period (date serial number)

Second argument

Number: Start day of the week (Monday: 1, Sunday: 7)

[Third argument]

Number: Reference day of the week for determining the week of the attribution period (same as above)

Returns the date serial number of the start of the week sequence, namely the first day of the first week, when the period starting on the attribution period start date is divided into weeks.

Attribution period start date

Specifies the date serial number of the first day of the period (year, month, etc.) you want to divide into weeks.

Start day of the week

Specifies which day of the week to start the week.

Reference day of the week for determining the week of the attribution period

Specifies to divide the weeks so that the first week includes the first occurrence of this day within the attribution period. If not specified, the week containing the attribution period start date is considered the first week.

@week_num

First argument

Number: Start of the week sequence (date serial number)

Second argument

Number: Date to determine (date serial number)

[Third argument]

Number: Start of the next week sequence (date serial number)

Returns the week number within the week sequence starting from the week sequence start date that includes the date to determine.
The week number of the first week is 1.

If "the start of the next week sequence" is specified, days following that date are considered to belong to the next week sequence (e.g., the next year). In that case, 0 is returned.

Logical Functions

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@is_error
(fusion_place >= 10.0)

First argument

Any value

Determines if the first argument is an error value. Returns "True" if it is an error value, otherwise "False".

Note: If the first argument is not an expression but a cell range specifier [A], the function result will not be "True" even if the cell range value is an error. This is because the cell value is an error value, not the cell range itself. Please specify member functions explicitly, such as [A].value, [A].total.

@iferror

First argument

Any value

Second argument

Value in case of error

Returns the second argument if the first argument is an error value. Otherwise, it returns the first argument.

Example:

  @iferror([A]/[B], "-")

In the example above, if the value of [B] ([B].total) is zero, resulting in division by zero, - is returned.

Error values include #DIV/0, #VALUE for numerical errors, and #N/A for values of cells that are not readable.

Note: If the first argument is not an expression but a cell range specifier [A], the function result will not be "True" even if the cell range value is an error. This is because the cell value is an error value, not the cell range itself. Please specify member functions explicitly, such as [A].value, [A].total.

Functions Available Only in Member Selection Expressions

The relative_position function is prepared for use with built-in member lists for fiscal years and relative periods. Although the opportunity to use the relative_position function may be rare, please refer to these built-in member lists if needed.

The depth and height functions, which are available only in member selection expressions, are different from the similarly named functions with arguments that can be used in form column, row, and cell expressions, so please be

cautious.
Function Arguments
(Optional arguments are enclosed in [])
Functionality

@relative_position

(None)

Returns the relative position of the current member based on the viewpoint member.

Available only in member selection expressions for fiscal year and relative period dimensions.

@depth
(fusion_place >= 12.1)

(None)

Returns the depth of the member currently being inspected (the current member) in member selection expressions. The calculation of depth starts from the root member of the member list (the depth of the root member is 0).

Members skipped in member selection expressions are also considered in the calculation of depth.

@height
(fusion_place >= 12.1)

(None)

Returns the height of the member currently being inspected (the current member) in member selection expressions. The calculation of height starts from the lowest member of the member tree (the height of the lowest member is 0). The height of members other than the lowest is the maximum height of its child members plus one.

Members skipped in member selection expressions are also considered in the calculation of height.

For details on the current member and the viewpoint member, please refer to Member Variables.

Functions Available in Form Column, Row, and Cell Expressions

For details on where these functions can be used, please refer to List of Text Expression Dialects.
Function Arguments
(Optional arguments are enclosed in [])
Functionality

@depth

First argument

Dimension object or String

Returns the depth (starting from 0) of the current member for the dimension specified by the first argument in the target column, row, or cell, if the current member is supplied from the vertical or horizontal member list. If there is no current member for the specified dimension, or if it is not supplied from the member list, 0 is returned.

Depth is counted by skipping members that are included in the member tree under the root member of the member list but not included in the member list itself. For example, if there are three levels of members A > B > C, and only the root A and its grandchild C are included in the member list, while B is not included, the depth of C is considered to be 1 (the depth of A is 0).

If the first argument is a string, it is considered a dimension label.

@height

First argument

Dimension object or String

Returns the height (starting from 0) of the current member for the dimension specified by the first argument in the target column, row, or cell, if the current member is supplied from the vertical or horizontal member list. If there is no current member for the specified dimension, or if it is not supplied from the member list, 0 is returned.

Height is counted by skipping members that are included in the member tree under the root member of the member list but not included in the member list itself. For example, if there are three levels of members A > B > C, and only the root A and its grandchild C are included in the member list, while B is not included, the height of A is considered to be 1 (the height of C is 0).

If a member appears more than once in the member list, the height is calculated for each occurrence.

If the first argument is a string, it is considered a dimension label.

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@participant_label

(None)

The label of the business unit itself that is reading/writing data.

Functions Available in Field Value Calculation Formulas

Function Arguments
(Optional arguments are enclosed in [])
Functionality

@field_key

[First argument]

Number: Field number

The value field key specified for the value field currently being processed. Multiple value field keys can be specified for a single value field, separated by commas. The first argument of @field_key specifies which key number to return (starting from 1).

If omitted, it is assumed that 1 is specified.

If a number less than or equal to 0 is specified, or if the specified number exceeds the number of field keys, or if a non-integer value is specified, the function returns an empty string.