In SuiteAnalytics Workbook, you can create custom formula fields to calculate values that are not available through standard record fields. You can add formula fields to your datasets on the Data Grid and use them to define criteria filters. You can also use them in any workbook visualizations that are based on the dataset. If you try to link two datasets but there are no fields with matching data types, you can also use formulas to cast the field values to the appropriate types.
The values of custom formula fields are updated each time you refresh the dataset or the connected workbook visualizations. Currently, you cannot create custom formula fields based on existing formula fields.
To create a custom formula field, you combine NetSuite fields and SQL formula functions in a formula definition. You can also customize some fields by using formula context values such as #converted. When you define a custom formula field, you must also select an output type for the formula, depending on the fields, functions, and context values you include in your definition. Workbook supports the following formula output types:
-
Boolean- Returns True, False, or NULL values
-
Date- Returns day, month, and year values
-
Datetime- Returns day, month, year, hour, minute, and second values
-
Float- Returns values that include a decimal, such as currency values
-
Integer- Returns values that are in whole numbers only, such count values
-
String- Returns fixed string character values, such as names
-
Clobtext- Returns long unicode character strings, such as item descriptions
-
Percent- Returns numeric values as percentages
-
Duration- Returns hour and minute values, such as hours worked
Knowledge of SQL will help you to fully leverage the supported SQL functions when you are defining a new formula. To read a description of each SQL function currently supported in the formula builder, click the Functions subtab of the Formula Field window. From the Functions subtab, you can also add a sample formula definition for each of the listed functions. Use this feature if you are not sure about which function to use in your formula definitions.
For more information about SQL formula functions, see SQL Expressions.
For more information about how to use formula fields, see Creating Formula Fields in SuiteAnalytics Workbook.
For sample formulas you can create, see Sample Formulas.
In your workbooks, you can also create calculated measures that you can use in your pivot tables and charts. However, using formula fields or calculated measures has an impact on the results that you obtain. To understand the difference between formula fields and calculated measures, see Formula Fields and Calculated Measures.