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 lets you to create custom formula fields using any functions 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 sections below for more sample formulas you can create using 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. So if you want to make a custom formula field that shows duration values as a number, use this formula:

            TO_NUMBER(TO_NCHAR({field_ID}))

For example, let’s say you want to see the time elapsed for each case in your account, in hours.

Example of calculating duration values in the Time Elapsed column.

In this case, use this formula 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, to show duration values in days and hours, first normalize the values with TO_NUMBER and TO_NCHAR, then use this formula. The output type 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 using different formula functions with the CONCAT function to calculate other 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

You may need to change a field to another data type before using it in a custom formula or running calculations. For example, if item price is an INTEGER and cost is a FLOAT, use this formula to get the difference:

            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 formulas in the table below if you need to change a field 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 in multiple currencies using the context values #currency_consolidated or #converted. In the Dataset Builder, you'll need this if you want to see totals or run calculations on fields with multiple currencies. In workbook visualizations, any field with values in more than one currency will have conversion options right in the 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 in the transaction accounting line record type, use this formula:

            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 #consolidated, you can also choose the specific currency code and exchange rate date if needed. For more information, see Currency Conversion Using Custom Formula Fields.

You always need to use TO_NUMBER with #currency_consolidated or #converted. That’s because the current Formula Builder doesn’t support the CURRENCY output type, so TO_NUMBER changes results to FLOAT. 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 making a formula field with the CASE WHEN function. In these formulas, if values meet the conditions in WHEN you get the result from THEN; all others use the result from ELSE.

For example, to find out which season a transaction occurred, use this formula. The output type 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.

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, here are some formulas you can use. The output type for each of these formulas is FLOAT:

Important:

If you're working with values in multiple currencies, make sure you consolidate or convert the field values to a single currency in your formula. For more information, see Currency Consolidation and Conversion Using Custom Formula Fields.

Note:

These samples work for formula fields in datasets based on the transaction record type. If you're working on a dataset based on the sales (ordered) or sales (invoiced) analytical record types, replace {foreigntotal} with {amountnet}. If you'd like to calculate other values, like quantity sold, replace {foreigntotal} with the right field ID.

After you've created your formulas, you can use them to make a calculated measure and analyze period-over-period variances. For more information, see Year over Year Growth.

Amount Last Year to Date

Use this formula to get transaction amounts from all of last year up to today:

              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 get transaction amounts from the start of this year up to today:

              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 get 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 get 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 get 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 get transaction amounts from 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 get amounts for transactions from the start of this month up to today:

              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 get transaction amounts from all of last month up to today:

              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 these formulas to calculate specific dates. The output type for each of these formulas is DATE:

First Day of This Month

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

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

Last Day of This Month

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

              LAST_DAY(CURRENT_DATE)

First Day of Last Month

Use this formula to get 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 get the last day of the last month:

              LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1))

Related Topics

General Notices