Custom Fields - an introduction
Create a Custom Field (custom-calculated column) to add and test out new useful fields in a single table- without affecting your source data.
All types of users can create fields that may be useful for analysis, and test out the best way to tidy their table data without the need for a developer's time. Custom fields let you create charts and build dashboards to meet the needs of 'the last mile' of data analytics.
Changes may include creating a new column that displays data without commas, has trimmed spaces, or replaces month names with numbers.
This isn't creating measures - it allows you to transform your table data in the best way for your dashboard.
Custom column key facts
Calculated columns are always added to a single table. You can't share the custom field across multiple tables.
You can create multiple custom fields for more than one table, but only one table at a time.
Custom fields are derived from the columns in each table.
Astrato transforms the data for every row for a new column that you create.
These changes are kept in the semantic layer. That is, the custom field is only saved in Astrato - not in the original database content. If you find it's a really useful field, you may want to add it to your source data to use as a dimension available to all your charts.
Creating a custom field
1. From the Data section, in the Define view, select the table you want to add your Custom Field to.
2. Click on the table to view its fields.
3. Click 'Custom Field' to view the Custom Column dialogue.
4. Click the Function button (fx) to select: String, Date, or Calculation, to create a new value based on your data.
5. Select the field(s) to apply the function to.
6. Name your custom field.
7. Add a description (optional).
8. Click Create.
Viewing your new column
1. After you click Create, you return to the view where you were before creating the custom field.
2. Click the Preview data tab.
3. Your new column is added as the last column in the table.
Editing or deleting a custom field
1. Click the Data icon to view your data tables.
2. Click on the table to view the fields in your table.
3. Scroll down to the bottom to view your custom fields.
4. The column on the right displays a 'trash' bin to delete the custom field, and a pencil to edit the measure.
Function options
Conditional operation choices include; string (text) function, date function, and calculation.
String functions
Concat - concatenation to combine text from multiple columns (e.g. address) - next field offers options for separators (space, colon, comma ... etc), select fields to apply
Replace - replace a single value (e.g. if it shows 'GB' - select a column and replace the value in that column)
Substring - extracts portions of text to reuse (eg bread and butter pudding > bread pudding)
Trim - use this function to adjust characters in a field
Date functions
Date outputs are in a numeric format, except when you select the options. This ensures you can use the field output for calculations, which you can't do with text (string).
If you select 'Quarter Year' - the output for Quarters in 2020 is:
2020 01 01
2020 04 01
2020 07 01
2020 10 01
List of options: Day, Day name, Week, Month, Month name, Quarter, Year, Month Year, Quarter Year.
Use cases:
Allows you to present date information in multiple formats
When a view of data on a dashboard using only numerical data is better.
You can also use this field for dimensions, calculations, filters, and aggregate on other date parts.
Calculation functions
The calculation options include: subtraction, addition, multiplication, and division. Output is always a number.
You can add as many functions to your calculation as you want - but they only work in a serial order. If you want to apply the order of operations - then you need to create a Custom Measure.
Use case:
Straightforward calculations