Creating a Calculated Column

The article talks about how to create a new column in your dataset by applying formulas to one or more columns in a physical or virtual dataset.

Introduction:

Calculated fields are very useful when we want to do something more with the collected data. For instance, consider the same scholarship data that we have on students. If we want to set labels for the scholarship amounts, for example,

Amount greater than equal to 150 --> Low

Amount between 151 and 500 --> Medium

Amount greater than 500 --> High

Now, this lets us summarize the results based on scholarship amount ranges. For example, we could see the percentage of students receiving Low, Medium, and High amounts of scholarships.

Steps to create calculated field:

1. Log in to your Impact Cloud instance and navigate to the datasets from the menu on the top (The screen looks like below)

2. To create a calculated field, click on the edit icon that appears when we hover over each of the datasets (below the actions column, shown in the screenshot above). We then see a popup like how it shows below then click on the "CALCULATED COLUMNS" tab

3. Click on "ADD ITEM", in this example we are going to create a calculated field that extracts year information from a date column that is basically interpreted as text.

• We then set the name of the column and the name of the label (these get displayed in visualizations and drop-downs)

SQL EXPRESSION field contains the SQL expression that will be applied to the calculated field.

The SQL code to extract year information from the date column is shown below. "Entry date" is the name of the original column/field that contains the date information.

(date_part('year', to_timestamp(demo_scholarship_data."Entry date",'dd/mm/yyyy')::TIMESTAMP))::text

The code results in the extraction of "2007" from a date such as "23/03/2007" and this rule is applied for every row in the table.