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:

  1. Hover over the table to which you want to add a field.
  2. Place the mouse cursor above the table name until the preference icons appear.
  3. Click the Add new custom field icon.

Add new cust field

  1. 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.
  1. To delete a custom field, click the field and click Remove.

tip

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.

Custom Field Use Cases

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.

daydiff (DueDate,ReceivedAt)

Price comparisons

Compare your price to the competitor’s price.

((CompetitorPrice-MyPrice)/((CompetitorPrice+MyPrice)/2))

Buckets

Create data buckets according to your required business break points.

CASE
WHEN [PriceVsCompetitor%] < -0.05 THEN '-5%'
WHEN [PriceVsCompetitor%] >= -0.05 AND [PriceVsAvgCompetitor%] <= 0.05 THEN '-5%/+5%'
WHEN [PriceVsCompetitor%] > 0.05 THEN '+5%'
end

Attributes

Create attributes to translate data talk to more coherent categories.

CASE
WHEN Region = 1 THEN 'USA'
WHEN Region = 2 THEN 'EUR'
WHEN Region = 3 THEN 'ASIA'
end

Date conversions

Convert dates into a numeric representation.

getyear(Date)*10000+getmonth(Date)*100+getday(Date) AS DateNum

Convert text into dates.

createdate(      toint('20'+RIGHT([Timesheet_Date],2)),
                     toint(LEFT(Timesheet_Date,2)),
                     toint(StrBetween(Timesheet_Date,'/','/'))
                  )

Date period comparisons over time

Create month to date and year to date calculations.

Click here to read our support article.

Surrogate keys

EmployeeID+tostring([DateNum])+tostring(CustomerID)+tostring([Project_ID])

Importing from another table

Use the lookup function to import a field from a different table. Click here to read more.