Adding a Custom Column
Sisense 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.
For a short video overview of adding a custom column to your data model, see below:
To add a new column:
- In the Navigation Pane, select > Add Custom Column.
In the ElastiCube model, select the table and click and Add Custom Column.
The new column will be added to the table, and an SQL Editor is displayed where you define your custom SQL expression.
- In the New Custom Column field, enter the name of your custom column and click .
- In the SQL Editor, enter your custom expression.
- Click to add your column to the table.
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.
Relative Difference Calculations
Figure out the relative difference between your price and competitor 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 column from a different table. See Adding a Custom Table to read more.