Adding calculated fields
Create calculated fields to transform your data by using one or more of the following:

Operators

Functions

Aggregate functions (you can only add these to an analysis)

Fields that contain data

Other calculated fields
You can add calculated fields to a dataset during data preparation or from the Analysis page. When you add a calculated field to a dataset during data preparation, it is available to all analyses that use that dataset. When you add a calculated field to a dataset in an analysis, it is available only in that analysis.
Analyses support both singlerow operations and aggregate operations. Singlerow operations are those that supply a potentially different result for every row. Aggregate operations supply results that are always the same for entire sets of rows. For example, if you use a simple string function with no conditions, it changes every row. If you use an aggregate function, it applies to all the rows in a group. If you ask for the total sales amount for the US, the same number applies to the entire set. If you ask for data on a particular state, the total sales amount changes to reflect your new grouping. It still provides one result for the entire set.
By creating the aggregated calculated field within the analysis, you can then drill down into the data. The value of that aggregated field is recalculated appropriately for each level. This type of aggregation is not possible during dataset preparation.
For example, if you want to figure out the percentage of profit for each country, region, and state, you can add a calculated field to your analysis, (sum(salesAmount  cost)) / sum(salesAmount). This field is then calculated for each country, region, and state, at the time your analyst drills down into the geography.
Prerequisites
 Your organization has a subscription to Calabrio Insights.
 You have the Insights > Create/edit report permission.
Page location
Insights > Analyses
Procedures
When you add a dataset to an analysis, every calculated field that exists in the dataset is added to the analysis. You can add additional calculated fields at the analysis level to create calculated fields that are available only in that analysis.
Add a calculated field to an analysis

In your analysis, click Add, and then click Add calculated field.

In the Calculations editor window, enter a name for the calculated field and then enter a formula using fields from your dataset, functions, and operators.

Click Save.
For more information about how to create formulas using the available functions in Insights, see Calculated field function and operator reference for Insights.
Insights authors can generate calculated fields during the data preparation phase of a dataset's creation. When you create a calculated field for a dataset, the field becomes a new column in the dataset. All analyses that use the dataset inherit the dataset's calculated fields.
If the calculated field operates at the row level and the dataset is stored in SPICE, Insights computes and materializes the result in SPICE. If the calculated field relies on an aggregation function, Insights retains the formula and performs the calculation when the analysis is generated. This type of calculated field is called an unmaterialized calculated field.
Add or edit a calculated field for a dataset

Open a dataset.

To create a new field, click Add calculated field.

To edit an existing calculated field, select a calculated field and then click Edit.

In the calculation editor, enter a name for the new calculated field. This name appears in the field list in the dataset, so it should look similar to the other fields.

(Optional) Add a comment, for example, to explain what the expression does, by enclosing text in slashes and asterisks.
Copy/* Calculates sales per year for this year*/

Identify the metrics, functions, and other items to use. For this example, we need to identify the following:

The metric to use

Functions: ifelse and datediff
We must build a statement such as "If the sale happened during this year, show the total sales and otherwise show 0."
To add the ifelse function, open the Functions list. Select All to close the list of all functions. Now, you should see the Aggregate, Conditional, Date function groups, and so on. Click Conditional and then doubleclick ifelse to add it to the workspace.
Copyifelse()


Place your cursor inside the parentheses in the workspace and add three blank lines.
Copyifelse(
) 
With your cursor on the first blank line, find the dateDiff function. It's listed for Functions under Dates. You can also find it by entering "date" for Search functions. The dateDiff function returns all functions that have "date" as part of their name. However, it does not return all functions listed under Dates. For example, the now function is missing from the search results. Doubleclick dateDiff to add it to the first blank line of the ifelse statement.
Copyifelse(
dateDiff()
) 
Add the parameters that dateDiff uses. Place your cursor inside the dateDiff parentheses, so that you can add date1, date2, and period:

For date1: The first parameter is the field that has the date in it. You can find it under Fields and add it to the workspace by doubleclicking it or entering its name.

For date2, add a comma, then choose truncDate() for Functions. Inside the parentheses, add period and date. The format is truncDate( "YYYY", now() )

For period: Add a comma after date2 and enter YYYY. This is the period for the year. To see a list of all the supported periods, find dateDiff in the Functions list, and open the documentation by clicking Learn more. If you are already viewing the documentation, see dateDiff.
You can add a few spaces for readability. Your expression must look like the below example.
Copyifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" )
) 

Enter the return value. For example, the first parameter in ifelse needs to return a value of TRUE or FALSE. Because we want the current year, and we are comparing it to this year, we specify that the dateDiff statement should return 0. The if part of the ifelse evaluates as true for rows where there is no difference between the year of the sale and the current year.
CopydateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0

To create a field for the total sales for last year, you can change 0 to 1.
Another way to do the same thing is to use addDateTime instead of truncDate. For each previous year, change the first parameter for addDateTime to represent each year. You can use 1 for last year, 2 for the year before that, and so on. If you use addDateTime, you leave the dateDiff function = 0 for each year.
CopydateDiff( {Discharge Date}, addDateTime(1, "YYYY", now() ) ,"YYYY" ) = 0 /* Last year */

Place your cursor on the first blank line under dateDiff and add a comma. For the then part of the ifelse statement, we need to choose the measure (metric) that contains the sales amount, TotalSales. To select a field, open the Fields list and doubleclick a field to add it to the screen. Alternatively, you can enter the name. Add { } around names that contain spaces. You can know which field is a metric by the number sign in front of it (#). Your expression should now look like the below example.
Copyifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0
,{TotalSales}
) 
Add an else clause. The ifelse function does not require one, but you must add it for reporting purposes. You cannot have any null values, because sometimes rows with nulls are omitted. You must set the else part of the ifelse to 0. The result is that this field is 0 for rows that contain sales from previous years. Add a comma and an 0 on the blank line. If you add the comment at the beginning, your finished ifelse expression should look similar to the below example.
Copy/* Calculates sales per year for this year*/
ifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0
,{TotalSales}
,0
) 
Click Save. If there are errors in your expression, the editor displays an error message. Check your expression for a red line and then hover your cursor over the line to see what the error message is. Common errors include missing punctuation, missing parameters, misspellings, and invalid data types. If you do not want to make changes, click Cancel.
Add a parameter value to a calculated field
You can reference parameters in calculated fields. By adding the parameter to your expression, you add the current value of the parameter.

To add a parameter, open the Parameters list and select the parameter whose value you want to include.

(Optional) To manually add a parameter to the expression, enter the name of the parameter. Add {} around the name and add a prefix of $, for example, ${parameterName}.
NOTE You can change the data type of any field in your dataset, including the types of calculated fields. You can only choose data types that match the data that is in the field.
Change the data type of a calculated field

For Calculated fields, select the field that you want to change, rightclick and click Change data type.
NOTE Unlike the other fields in the dataset, you cannot disable calculated fields. Instead, you must delete them.
To delete a calculated field

For Calculated fields, select the field that you want to change, rightclick and click Delete.
When your dataset uses the Direct Query mode, the calculation of the decimal data type is determined by the behavior of the source engine that the dataset originates from. In some cases, Insights applies special handlings to determine the output calculation's data type.
When your dataset uses SPICE query mode and a calculated field is materialized, the data type of the result is contingent on the specific function operators and the data type of the input. The tables below show the expected behavior for some numeric calculated fields.
The following unary operators table shows which data type is output based on the operator you use and the data type of the value that you input. For example, if you input an integer to an abs calculation, the output value's data type is an integer.
Operator  Input type  Output type 

abs  Decimalfixed  Decimalfixed 
Int  Int  
Decimalfloat  Decimalfloat  
ceil  Decimalfixed  Int 
Int  Int  
Decimalfloat  Int  
exp

Decimalfixed  Decimalfloat 
Int  Decimalfloat  
Decimalfloat 
Decimalfloat 

floor

Decimalfixed 
Int 
Int 
Int 

Decimalfloat 
Int 

ln

Decimalfixed 
Decimalfloat 
Int 
Decimalfloat 

Decimalfloat 
Decimalfloat 

log

Decimalfixed 
Decimalfloat 
Int 
Decimalfloat 

Decimalfloat 
Decimalfloat 

round

Decimalfixed 
Decimalfixed 
Int 
Decimalfixed 

Decimalfloat 
Decimalfixed 

sqrt

Decimalfixed 
Decimalfloat 
Int 
Decimalfloat 

Decimalfloat 
Decimalfloat 
The following binary operators tables show which data type is output based on the data types of the two values that you input. For example, for an arithmetic operator, if you provide two integer data types, the result of the calculation output is an integer.
For basic operators (+, , *):
Integer  Decimalfixed  Decimalfloat  

Integer  Integer  Decimalfixed  Decimalfloat 
Decimalfixed  Decimalfixed  Decimalfixed  Decimalfloat 
Decimalfloat  Decimalfloat  Decimalfloat  Decimalfloat 
For division operators (/):
Integer  Decimalfixed  Decimalfloat  

Integer  Decimalfloat  Decimalfloat  Decimalfloat 
Decimalfixed  Decimalfloat  Decimalfixed  Decimalfloat 
Decimalfloat  Decimalfloat  Decimalfloat  Decimalfloat 
For exponential and mod operators (^, %):
Integer  Decimalfixed  Decimalfloat  

Integer  Decimalfloat  Decimalfloat  Decimalfloat 
Decimalfixed  Decimalfloat  Decimalfloat  Decimalfloat 
Decimalfloat  Decimalfloat  Decimalfloat  Decimalfloat 