The ElastiCube Manager makes it easy for you to add new fields to existing tables. This can be useful if you need to combine data from different existing fields, and when you need to cleanse and prepare data. The new fields also provide designers with additional fields to use as is, or as a basis for even more advanced calculations in their widgets. You can use SQL to customize the values contained within the custom fields. See more examples of when to create custom fields.
To add a new field:
- Hover over the table to which you want to add a field.
- Place the mouse cursor above the table name until the preference icons appear.
- Click the Add new custom field icon.
- The new field will be added to the table, and the field’s settings window appears, with the following options:
- Name: Click the field name in the settings window to rename or edit the field name.
- Type: Select the field type from the drop-down list. Field types should be based on the data stored in the new field. For example text data must be set to text in the Type field, while date -time data must be set to Date-Time.
- Expression: You can use SQL to customize the data contained in the new field. Click Edit to edit and view the field expression. See Defining and Editing Field Formula for further details on developing field expressions.
- To delete a custom field, click the field and click Remove.
Field data can be converted to a different type using functions. For example, a number can be converted to a text field. See the function reference for more details.
Here are some typical scenarios for creating custom fields.
Row level calculations
Calculate revenue from your sales data.
Gross Revenue = (Unit Price * QuantitySold)
Net Revenue = (Unit Price * QuantitySold) - (Unit Cost * QuantityPurchased)
Time difference calculations
Work out the arrival time based on due and actual arrival dates.
Compare your price to the competitor’s price.
Create data buckets according to your required business break points.
WHEN [PriceVsCompetitor%] < -0.05 THEN '-5%'
WHEN [PriceVsCompetitor%] >= -0.05 AND [PriceVsAvgCompetitor%] <= 0.05 THEN '-5%/+5%'
WHEN [PriceVsCompetitor%] > 0.05 THEN '+5%'
Create attributes to translate data talk to more coherent categories.
WHEN Region = 1 THEN 'USA'
WHEN Region = 2 THEN 'EUR'
WHEN Region = 3 THEN 'ASIA'
Convert dates into a numeric representation.
getyear(Date)*10000+getmonth(Date)*100+getday(Date) AS DateNum
Convert text into dates.
Date period comparisons over time
Create month to date and year to date calculations.
Click here to read our support article.
Importing from another table
Use the lookup function to import a field from a different table. Click here to read more.
Hey! Was this article helpful?
Questions? Ask the community.