Why don't I see the aggregate option I want when creating a new aggregation field?

Modeling
1

Sometimes we don’t aggregate a specific field when we generate the view. This is often because we don’t recognize the type (for example, we think it’s a string or some unknown type rather than a number or a date).

Example:
You have a “raw_date” field that is generated as a string type instead of a date type.

To generate the correctly formatted field, you can edit a field’s SQL from the workbook that casts the field to the correct formatting:

  1. Navigate to the field’s options (hover and select the vertical three-dot menu to the right of the field name)
  2. Click on Modeling > Edit.
  3. Input the SQL in your database’s dialect to cast the field as the type you expect to see.
    • e.g. use CAST(raw_date AS DATE) in the custom field SQL to alter the raw_date field to have date formatting

Alternatively, you can edit the field in the model by…

  1. Navigating to the field’s menu options > select Go to definition
  2. Add a sql parameter and enter the sql desired to change the formatting.

Before:

raw_date: {}

After:

raw_date:
    sql: CAST(raw_date AS DATE)

Use your database SQL dialect documentation to determine the syntax needed for casting to different field types.