I would like to encourage more SuiteAnalytic users to use calculated fields, known as "formula fields" in Workbook UI. It can help you to achieve the goal for your report or data analysis. But I know that sometimes it's not easy to understand the syntax and error messages formula builder produces.
I've prepared few tips on how to construct your formula/calculated fields in Workbook and don't end up with an error message.
One of the typical complications is working with multiple type of fields. In such case, you'll usually need to convert some fields so they're compatible for the mutual mathematical operation you need. Formula builder in Workbooks makes some conversion automatically and for others you've to use the right casting function. The table below will help you to understand where it's necessary to use conversion/cast function and for what data types it's done automatically.
Data type casting in Workbook calculated fields/formulas
Field type
| to string conversion
| to integer (no decimal point) number conversion
| to float (decimal point) number conversion | Other
|
|---|
| INTEGER | use: TO_CHAR({field_id}) | it's same type | automatic conversion | |
| FLOAT | use: TO_CHAR({field_id}) | use one of the following functions: CEIL({field_id}), FLOOR(..), ROUND(..), TRUNC(..). | it's same type | |
| BOOLEAN | automatic (returns "T" or "F") | use following syntax: CASE field WHEN 'T' THEN 1 ELSE 0 END | use following syntax: CASE field WHEN 'T' THEN 1 ELSE 0 END | |
| DATE | use: TO_CHAR({field_id}) | not available | not available | automatic cast to DATETIME type |
| DATETIME | use: TO_CHAR({field_id}) | not available | not available | use CAST_TO_DATE({field_id}) for conversion to DATE type |
| PERCENT | use: TO_CHAR({field_id}) (it returns range from 0 to 1) | not available/recommended (possible but you'd get 0 or 1 values only due to rounding/ceiling) | use: TO_NUMBER({Field_id}) | |
| DURATION | use: TO_CHAR({field_id}) (it returns values as float number, not the hours:minutes format) | not available | use: TO_NUMBER({Field_id}) | |
| STRING | it's same type | use: TO_NUMBER({Field_id}) (usually not recommended since most of conversions have floating point). | use: TO_NUMBER({Field_id}) | |
| CURRENCY | use: TO_CHAR({field_id}) (it returns only a number without the currency symbol) | not available/recommended | use: TO_NUMBER({Field_id}) | |
| CURRENCY_HIGH_PRECISION | use: TO_CHAR({field_id}) (it returns only a number without the currency symbol) | not available/recommended | use: TO_NUMBER({Field_id}) | |
Second area, where the confusion typically starts is the formula output type compatibility. Each new formula/calculated field asks you for its output type, that means you need to select on of the options and decide how you want the system to operate with your new field (string, number, date...). It would be ideal if this is done automatically in most cases, and we are working on this, however now you have to simply select one of the options and that's why you may get an error message on our formula syntax.
Below is the compatibility table, showing you, what is the required output data type for the standard mathematical operations and fields you may use:
Compatibility of the output formula data type and standard operations
| Operation | First field data type
| Second field data type
| Resulting data type |
|---|
| "/" - division | FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT |
| "+" - subtraction | INTEGER | INTEGER | INTEGER |
| FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT |
| DATE | DATE | FLOAT |
| DATETIME | DATETIME | not supported |
| DATE | FLOAT | DATE |
| "*" - multiplication | INTEGER | ITNEGER | ITNEGER |
| INTEGER | FLOAT | FLOAT |
| "+" - addition | INTEGER | INTEGER | INTEGER |
| FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT, INTEGER, DURATION, CURRENCY, CURRENCY_HIGH_PRECISION, PERCENT | FLOAT |
| DATE | FLOAT | DATE |
| | | |
Remember, look at the first table when you work with various data type fields and look for the automatic vs. manual ways of the type conversion. The second table is for a setting of the output formula type as a result of your formula operation.
Here are few examples how to use formulas:
1:
This converts "STRING" field to a number and adds up a constant as a "FLOAT" formula result:
TO_NUMBER('33.5') + 10
2:
This takes two "DATETIME" fields, converts them to "DATE" type and creates a difference as "FLOAT". Where the result means amount of days and remaining percentage of a day:
CAST_TO_DATE({datecreated}) - CAST_TO_DATE({startdate}) formula on record type “Case” and it returns float value as a diff. Where the diff/result is days and the remaining percentage of a day.
Example 6/4/2010 12:33 – 6/4/2010 returns 0.52 which means the diff is 52% from 24 hours.
3:
This uses "CASE" condition evaluation and returns a "STRING" type formula based on the evaluation of the "CURRENCY" field type. The "CURRENCY" field type needs to be converted via "TO_NUMBER" to be compared with an "INTEGER" (100 in our case):
CASE WHEN (TO_NUMBER({foreigntotal}) < 100)
THEN 'too low'
ELSE 'good deal'
END
Example of nested case
CASE WHEN({entity<transaction.leadsource#display} = 'Other') THEN 'Find what the Other source is'
WHEN ({searchstage}) <> 'Lead' THEN
CASE WHEN ({searchstage} = 'Prospect' OR {searchstage} = 'Customer') THEN
CASE WHEN ({entity<transaction.leadsource#display} = 'Electronics Retail Trade Show') THEN 'Do more electronic trade shows'
WHEN ({entity<transaction.leadsource#display} = 'Trade Show') THEN 'Do more good trade shows'
ELSE 'we were lucky'
END
END
END
4:
This takes field with type "Duration" and makes it a "STRING" result with Days an hours:
CONCAT('Days: ',TO_NCHAR(FLOOR({timeelapsed}/24)),
' Hours: ',TO_NCHAR(MOD(TO_NUMBER({timeelapsed}),24)))
I hope this helps a bit for your better understanding of how the Workbook formulas work with data types.
If you have a problem, just drop me a line in this thread so we can expand the examples.