Overview

Calculated fields provide a way to perform calculations and more advanced logic against existing fields. This can be as simple as adding a new field that subtracts the values in other fields or as complex as integrating case statements to perform certain business logic based on the values of fields within the table. This article will cover the different types of Calculated Fields and how to use them.

Details

Calculated Fields can be powerful ways to work with and clean up data that is brought in through other fields in the data source. When using Calculated Fields, you will choose the table that you want to create the calculated field in, specify the type of calculation and associated parameters, and a new field will be created within the table. When the table is processed in the pipeline, the calculations specified for the field will be processed and the resulting value will be placed in the new field.

Adding a Calculated Field

To add a calculated field, navigate to the Transformations section within Data Management and identify the table that the calculated field should be added to. Click the + to the right of the field name and select Calculated Field from the list.

This will create a field named "Calculated Field" in the table. Clicking on the field will show the Calculated Field section with the parameters that can be defined. 

To rename the field, click on the pencil icon to the right of the Name field, type in the new field name, and click Save.

Clicking the Add Step button will add the first step in the calculated field. It is possible to add multiple steps, with each one being able to do additional calculations on the results of the previous steps.

You can adjust the order of the steps by clicking the "drag handle" on the left and dragging each step above or below other steps in the calculated field definition.

To delete a step, click the - button on the right side of the step to be deleted.

Using the "Previous" Parameter

When working with multiple steps, you can utilize a "previous" parameter as the input so that Cosmos will know to take the output from the previous step and use it as the input in the current step. You can add this to any input by typing @previous in the input field. Once the @previous parameter is selected, you can perform other operations on it. The example below takes the output from the previous step and adds 10 to it.

Working with Mathematical Calculations

In many cases, you may want to do mathematical calculations on fields to add multiple fields together, multiple a price by a quantity, etc. The following mathematical operators are supported when using calculated fields:

• Add
• Subtract
• Multiply
• Divide

You can change the calculation type to use by clicking the Type dropdown field and selecting the desired operation from the list. 

In the example below, Cosmos will take the number 100 and add 20 to it in the first operation. Next, it will take the result of that (120) and subtract 30. Finally, it will take the result of the previous step (120 - 30 = 90) and multiply it by 2, for a result of 180. 

Working with Case Statements

Case statements can be especially useful for building out conditional logic within your data. Case statements enable you to define a field to look at, specify values that you would like to have conditions for, and then what the output should be when those values show up in the data, and be able specify an "ELSE" condition that will be returned for everything else that doesn't match one of the conditions that you specified. This is like the use of CASE within SQL statements. 

In the example below, we are creating a new field called "Region". For any customers in the state of WA or CA we want to set the region to "West". For any customers in the state of NY or FL we want to set the state to "East".

First, we will set the calculated field Type to be "Case". Next, we will set the Input field to be "State", since that is the field that we want Cosmos to look at. Since "State" is a text field we will set the Input Type to "Text". 

Next, we will specify the conditions that we want to look for by clicking the Add Match Condition button. We will add a new match condition for each of the scenarios that we want to specify. In the example below, the first match condition is where "State" = WA and, if this is the case, we want Cosmos to associate it with "West". We do this by typing WA in the Match Condition field and by typing West in the Output field. 

For the Output field you can type in text or select a field in the table. If you select a field in the table, if the condition matches, the value of the specified field for each matching record will be used. 

In the example below, the rest of the states have been defined.

Lastly, for any state that doesn't match one of the four that we have defined, we want to set the region value to be "No Region". To do this, we will set the Output Type to match the Input Type field (in this case, "Text") and then put the "ELSE" condition in the Default Output field. This represents the value that will be returned for all records that don't match the conditions that we specified.

When the table is processed the following will happen:

• Any record that has a State of WA or CA will have "West" be returned in the Region field
• Any record that has a State of NY or FL will have "East" be returned in the Region field
• Any record that has a State of any value not defined (WA, CA, NY, or FL) will have "No Region" be returned in the Region field

Converting Null Values to Empty

In some cases, a byproduct of working with field transformations may be that a certain record may have a NULL value set for a field. For example, if we have a Customer with a "Country Code" that is blank, if we go to do a look up to a "Country" table and there is no corresponding record where the "Code" is also blank, no match may be found, so the lookup for Country Name will not have anything to return and the Country Name field for that customer will be NULL.

For the purposes of filtering in reports, NULL and blank are not the same, so any user that is filtering on a blank will not see records returned if the value is NULL. To clean up the data, it is possible to specify that a particular field should have any NULL values converted to empty strings. 

To do this, you can add a Calculated Field to the table and set the Type to be "Null to Empty". Then in the Input field you would specify the field to look at to determine if there are any NULL values. 

In the example above, there is a calculated field named "Country Name Clean" that has been added. This will look at the "Country Name" field and, if any NULL values are present, these will be converted to empty strings in the "Country Name Clean" field. The "Country Name Clean" field is the one that would then be mapped into the reporting tables. 

Removing Hours and Minutes from Dates

There are certain scenarios where a field that is being returned from a data source has a date field that includes hours and minutes, but you may only want to return the date. 

To clean up the contents of the field, you can add a calculated field and set the Type to be "Date Only". You can then set the Input field to point to the date field that you would like to clean up. 

In the example above, a calculated field named "Posting Date Clean" has been added that will look at the "Posting Date" field in the table and zero out the hours and minute, if present. Below is an example of what the original value may look like in the "Posting Date" field, and what the updated value would look like in the "Posting Date Clean" field after the table has been processed.

Original Date Value: 2022-05-08 12:35:00

Updated Date Value: 2022-05-08 00:00:00