Adding a Custom Column

The ElastiCube Manager makes it easy for you to add new columns to existing tables. This can be useful if you need to combine data from different existing columns, and when you need to cleanse and prepare data. The new columns 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 columns.

To add a new column:

  1. In the left pane, select > Add Custom Column. Click here to learn more.

    OR
    In the schema, select the table and click and Add Custom Column.
  2. The new column will be added to the table, and the column's settings window is displayed with the following options:

To delete a custom field, click the field and click Remove.

Note: Column 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 column from a different table. Click here to read more.