What can I do with a calculated column?

Calculated columns are useful for deriving values from the original dataset columns (such as calculating someone's age based on their birth date), doing data cleansing and pre-processing (such as combining column values or substituting one value for another), or for computing new data values based on a number of input variables (such as calculating a profit margin value based on revenue and costs).

Can I refer to a calculated column in another calculated column formula?

No. Calculated column formulas can operate only on the original dataset columns.

What formulas can I use to define a calculated column?

The formulas that describe a calculated column must be valid SQL expressions that:

• Would be valid as an item in the column list of a SELECT statement.
• Use SQL functions and expression syntax supported by the engine you are using to query your data warehouse.
• Do not require an input or output type that AtScale doesn't support. (For example, SQL functions that operate on map or array data types.)

Can I use aggregate functions in a calculated column formula?

No. Although it is valid SQL, it is not the correct way to add aggregations to an AtScale model. Instead, define a metric or a calculated member using the aggregate calculations that AtScale supports.

What SQL operators and functions can I use in a calculated column?

If you are using Google BigQuery as your data warehouse, see Functions & Operators in the BigQuery documentation.

Is the AS statement supported?

Yes, except when using InterSystems IRIS as a data warehouse.

The AS (alias) statement is not supported by InterSystems IRIS, and you should not use it in the formula for a calculated column. Using it with such a data warehouse would lead to failed queries with "Unsupported CAST target specified" message.