1. SuiteAnalytics
  2. Workbook
  3. Custom Workbooks and Datasets
  4. Defining a Dataset
  5. Formula Fields
  6. Advanced Sample Formula Fields

Advanced Sample Formula Fields

The SuiteAnalytics Workbook Formula Builder enables you to create custom formula fields using any of the formula functions listed in the SuiteAnalyticsWorkbookFormulaFunctions.xls spreadsheet. You can add sample formulas for any of these functions directly from the Functions subtab in the Formula Builder using the Add the example to the formula link. See the following sections for additional sample formulas that you can create using some of the available functions:

For more sample formulas, see Search Formula Examples and Tips.

For information about using custom formula fields for currency conversion or consolidation, see Currency Conversion Using Custom Formula Fields.

Calculating Duration Values with TO_NUMBER and TO_NCHAR

Duration values are incompatible with the TO_NUMBER function unless they are first converted to a variable format using the TO_NCHAR function. Consequently, if you want to create a custom formula field that displays duration values as a number, you must use the following formula definition:

            TO_NUMBER(TO_NCHAR({field_ID}))

For example, assume you want to see the amount of time that has elapsed for each case in your account, in hours.

Example of calculating duration values in the Time Elapsed column.

In this case, use the following formula definition with an output type of Float:

            TO_NUMBER(TO_NCHAR({timeelapsed}))
Time Elapsed (Hours) selected in the Formula Field Name fields. Time elapsed shown in the dataset.

Combining CONCAT and other Functions to Calculate String Values

You can combine the CONCAT function with other functions to calculate complex string values. For example, if you want to view the duration values for all the cases in your account in days and hours, you must normalize the values with TO_NUMBER and TO_NCHAR, then concatenate them using the following definition. The output type for this formula definition is STRING:

            CONCAT(CONCAT('Days: ',TO_NCHAR(FLOOR(TO_NUMBER(TO_NCHAR({timeelapsed}/24))))),
CONCAT(' Hours: ',TO_NCHAR(MOD(TO_NUMBER(TO_NCHAR({timeelapsed})),24))))
Example of combining CONCAT and other functions. CONCAT formula shown in the dataset.

Try combining different formula functions with the CONCAT function to calculate different string values.

Casting Field Values using TO_NUMBER and TO_NCHAR

Fields in NetSuite store values in different data types, such as STRING, INTEGER, and FLOAT.

  • String- text values, such as a customer name

  • Integer- positive or negative whole numbers that do not contain a decimal, such as counts

  • Float- numeric values that can contain a decimal, such as amounts

Some fields need to be cast to a different data type before you can use them in a custom formula field, or before you can perform arithmetic operations with them. For example, if item price was stored as an INTEGER value and item cost was stored as a FLOAT value, you could use the following formula definition to calculate the difference between the two:

            TO_NUMBER({item<pricing.unitprice}) - TO_NUMBER({cost})
Casting values using TO_NUMBER and TO_CHAR. Possible Profit column on the Item Dataset page.

Use the formula definitions in the following table when you want to cast fields to a different data type:

Data Type

To cast to STRING

To cast to INTEGER

To cast to FLOAT

INTEGER

TO_CHAR({field_ID})

—

Automatic casting

FLOAT

TO_CHAR({field_ID})

CEIL({field_ID})

FLOOR({field_ID})

ROUND({field_ID})

TRUNC({field_ID})

—

BOOLEAN

Automatic casting, returns ‘T’ or ‘F’

CASE field WHEN ‘T’ THEN 1 ELSE 0 END

CASE field WHEN ‘T’ THEN 1 ELSE 0 END

DATE

TO_CHAR({field_ID})

—

—

DATETIME

TO_CHAR({field_ID})

—

—

PERCENT

TO_CHAR({field_ID})

Returns values between 0 and 1

—

TO_NUMBER({field_ID})

DURATION

TO_CHAR({field_ID})

Returns FLOAT values, not in the hours:minutes format

—

TO_NUMBER({field_ID})

STRING

—

TO_NUMBER({field_ID})

Not preferred

TO_NUMBER({field_ID})

CURRENCY

TO_CHAR({field_ID})

Returns values without the currency symbol

—

TO_NUMBER({field_ID})

CURRENCY_HIGH_PRECISION

TO_CHAR({field_ID})

Returns values without the currency symbol

—

TO_NUMBER({field_ID})

Currency Consolidation and Conversion Using Custom Formula Fields

You can consolidate or convert values that are in multiple currencies using the context values #currency_consolidated or #converted. In the Dataset Builder, this is required if you want to view totals or perform arithmetic operations using amount fields that have values in multiple currencies. In workbook visualizations, all fields with values in multiple currencies have consolidation and conversion options available directly from the user interface. For more information, see Currency in Datasets and Workbooks.

In Workbook, currency consolidation is only applicable to amount fields from the transaction accounting line record type. Currency conversion is only applicable to amount fields from the transaction and transaction line record types.

For example, to consolidate values from the amount net field on the transaction accounting line record type, use the following definition:

            TO_NUMBER({transactionlines.accountingimpact.netamount#currency_consolidated})

To convert the values from the amount paid field on the transaction record type, use this definition:

            TO_NUMBER({foreignamountpaid#converted})

With the context value #consolidated, there are also additional options to specify the exact currency code and exchange rate date to use for the conversion. For more information, see Currency Conversion Using Custom Formula Fields.

The function TO_NUMBER is always required when you use #currency_consolidated or #converted. This is because the current version of the Formula Builder does not support the CURRENCY output type and TO_NUMBER casts the results to the FLOAT output type. For more information about using TO_NUMBER to cast currency values, see Casting Field Values using TO_NUMBER and TO_NCHAR.

Conditional Evaluations Using CASE WHEN

You can perform conditional evaluations by creating a formula field using the CASE WHEN function. In these formulas, values that meet the conditions you set in the WHEN statement produce the results you define in the THEN statement. Values that do not meet the conditions in the WHEN statement produce the results that you define in the ELSE statement.

For example, if you want to determine the season that a transaction took place, you can use the following formula definition. The output type for this formula is STRING:

            CASE
 WHEN EXTRACT(Month FROM {trandate})= 12 THEN 'winter'
 WHEN EXTRACT(Month FROM {trandate})= 6 THEN 'summer'
ELSE 'it was fall or spring' END
Example using CASE WHEN formulas. New Season formula shown in the dataset.

In Workbook, you can nest different CASE WHEN statements within the same formula definition.

Calculating Amounts for Relative Date Ranges

You can create formula fields to calculate amounts for relative date ranges. This includes transaction sales amounts, quantities, and other numerical values. Depending on the date range, you can use the following formulas. The output type for each of these formulas is FLOAT:

Important:

If you are working with values in multiple currencies, you must consolidate or convert the field values to a single currency as part of your formula definition. For more information, see Currency Consolidation and Conversion Using Custom Formula Fields.

Note:

The following samples are valid for formula fields in datasets based on the transaction record type. If you working on a dataset based on the sales (ordered) or sales (invoiced) analytical record types, replace {foreigntotal} with {amountnet}. If you want to calculate other values like quantity sold, replace {foreigntotal] with the appropriate field ID.

After you create your formulas, you can use them to create a calculated measure to analyze period over period variances. For more information, see Year over Year Growth.

Amount Last Year to Date

Use this formula to calculate transaction amounts from all of last year until today's date:

              CASE WHEN (ADD_MONTHS({trandate}, 12) <= CURRENT_DATE) AND (EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE)) THEN TO_NUMBER({foreigntotal})
END

Amount This Year to Date

Use this formula to calculate transaction amounts for transactions created from the beginning of this year to today's date:

              CASE WHEN {trandate} <= CURRENT_DATE AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount This Year

Use this formula to calculate the total amounts for transactions created this year:

              CASE WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN {foreigntotal}
END

Amount Last Year

Use this formula to calculate the total amounts for transactions created last year:

              CASE WHEN EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount This Quarter to Date

Use this formula to calculate amounts for transactions created during this quarter:

              CASE WHEN TO_CHAR({trandate},'Q') = TO_CHAR(CURRENT_DATE,'Q') AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount Last Quarter

Use this formula to calculate transaction amounts from all of last quarter

              CASE
 WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) > 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q'))-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
 
 WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) = 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = 4 AND EXTRACT(YEAR FROM {trandate})+1 = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END

Amount This Month to Date

Use this formula to calculate amounts for transactions created from the beginning of this month to today's date:

              CASE
 WHEN {trandate} <= CURRENT_DATE AND EXTRACT(Month FROM {trandate}) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END

Amount Last Month to Date

Use this formula to calculate transaction amounts from all of last month until today's date:

              CASE
 WHEN (ADD_MONTHS({trandate}, 1) <= CURRENT_DATE) AND EXTRACT(Month FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END

Calculating Specific Dates

Use the following formula definitions to calculate specific dates. The output type for each of these formulas is DATE:

First Day of This Month

Use this formula to calculate the first day of the month:

              TRUNC(LAST_DAY(CURRENT_DATE)-1, 'MONTH')

Last Day of This Month

Use this formula to calculate the last day of the month:

              LAST_DAY(CURRENT_DATE)

First Day of Last Month

Use this formula to calculate the first day of the last month:

              TRUNC(LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)), 'MONTH')

Last Day of Last Month

Use this formula to calculate the last day of the last month:

              LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1))

Related Topics

General Notices