Advanced Sample 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.

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

            TO_NUMBER(TO_NCHAR({timeelapsed})) 

          

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)))) 

          

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}) 

          

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

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 

          

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:

• Amount Last Year to Date

• Amount This Year to Date

• Amount This Year

• Amount Last Year

• Amount This Quarter to Date

• Amount Last Quarter

• Amount This Month to Date

• Amount Last Month to Date

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.

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

            

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

            

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

            

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

            

              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 

            

              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 

            

              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 

            

              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

• Last Day of This Month

• First Day of Last Month

• Last Day of Last Month

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

            

              LAST_DAY(CURRENT_DATE) 

            

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

            

              LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1)) 

            

Related Topics