Working with Calculated Fields in Datasets - Amazon QuickSight

# Working with Calculated Fields in Datasets

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

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's available to all analyses that use that dataset. When you add a calculated field to a dataset in an analysis, it's available only in that analysis. For information about adding calculated fields in an analysis instead, see Adding a Calculated Field to an Analysis.

Topics

## Handling Decimal Values in Calculated Fields

The decimal data type supports up to four decimal places to the right of the decimal point. During data preparation, calculated fields that use decimal data with more than four decimal places use the full value to perform the calculation. If the result is again decimal data that uses more than four decimal places, the result is then truncated when the dataset is imported into SPICE or displayed in an analysis.

As an example, take decimal field Field_A with a value of 0.00006, which is displayed in the user interface as 0.0. The full value 0.00006 is still used in all calculations. The following are some examples of how you can use this value in calculations:

• Field_A > 0 = true. The calculated field value displayed in the analysis or imported into SPICE is `true`.

• ceil(Field_A) = 1. The calculated field value displayed in the analysis or imported into SPICE is `1`.

• Field_A + 0.00009 = 0.00015. The calculated field value displayed in the analysis or imported into SPICE is `0.0001`.

• Field_A * 1.5 = 0.00009. The calculated field value displayed in the analysis or imported into SPICE is `0.0`.

## Using the Calculations Editor

Create calculated fields to transform your data before you analyze it. For details about supported functions and operators, see Calculated Field Function and Operator Reference for Amazon QuickSight .

You can add calculated fields directly to a dataset. The fields that you add become available to anyone who uses the dataset. When you use the dataset in an analysis, you can add additional calculated fields. The fields that you add to an analysis are available only in that analysis. For information about adding fields to an analysis instead, see Adding a Calculated Field to an Analysis.

To add or edit a calculated field

1. Open the dataset that you want to work with.

To open a dataset from within an analysis, open the list by choosing the pencil icon near the dataset at left. Choose the menu ellipses ( `…` ). Then choose Edit. This action opens the dataset editor, also called the data preparation screen. In this screen, you can add a field directly to the dataset, rather than adding it only to the analysis.

2. Open the expression editor for calculated fields by doing one of the following:

• To create a new field, choose Add calculated field at left.

• To edit an existing field, choose it from Calculated fields at left, then choose Edit from the context (right-click) menu. 3. Enter a descriptive name for Add title to name the new calculated field. This name appears in the field list in the dataset, so it should look similar to the other fields. For this example, we name the field `Total Sales This Year`.

4. (Optional) Add a comment, for example to explain what the expression does, by enclosing text in slashes and asterisks.

``/* Calculates sales per year for this year*/``
5. 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 want to build a statement like "If the sale happened during this year, show the total sales, and otherwise show 0."

To add the `ifelse` function, open the Functions list. Choose All to close the list of all functions. Now you should see the function groups: Aggregate, Conditional, Date, and so on.

Choose Conditional, and then double-click on `ifelse` to add it to the workspace.

``ifelse()``
6. Place your cursor inside the parenthesis in the workspace, and add three blank lines.

``````ifelse(

)``````
7. 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. It doesn't return all functions listed under Dates; for example, the `now` function is missing from the search results.

Double-click on `dateDiff` to add it to the first blank line of the `ifelse` statement.

``````ifelse(
dateDiff()

)``````
8. Add the parameters that `dateDiff` uses. Place your cursor inside the `dateDiff` parentheses to begin to add `date1`, `date2`, and `period`:

1. For `date1`, the first parameter is the field that has the date in it. Find it under Fields, and add it to the workspace by double-clicking it or entering its name.

2. For `date2`, add a comma, then choose `truncDate()` for Functions. Inside its parenthesis, add period and date, like this: `truncDate( "YYYY", now() )`

3. 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 choosing Learn more. If you're already viewing the documentation, as you are now, see dateDiff.

``````ifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" )

)``````
9. Specify the return value. For our example, the first parameter in an `ifelse` needs to return a value of `TRUE` or `FALSE`. Because we want the current year, and we're 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.

``   dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0  ``

To create a field for `TotalSales` for last year, you can change `0` to `1`.

Another way to do the same thing is to use `addDateTime` instead of `truncDate`. Then for each previous year, you change the first parameter for `addDateTime` to represent each year. For this, you 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.

``   dateDiff( {Discharge Date}, addDateTime(-1, "YYYY", now() ) ,"YYYY" ) = 0 /* Last year */``
10. Move your cursor to the first blank line, just under `dateDiff`. 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 choose a field, open the Fields list and double-click a field to add it to the screen. Or you can enter the name. Add curly braces `{ }` around names that contain spaces. It's likely that your metric has a different name. You can know which field is a metric by the number sign in front of it ( # ).

Your expression should look like the following now.

``````ifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0
,{TotalSales}

)``````
11. Add an `else` clause. The `ifelse` function doesn't require one, but we want to add it. For reporting purposes, you usually don't want to have any null values, because sometimes rows with nulls are omitted.

We set the `else` clause of `ifelse` to `0`. The result is that this field is `0` for rows that contain sales from previous years.

To do this, on the blank line add a comma and then a `0`. If you added the comment at the beginning, your finished `ifelse` expression should look like the following.

``````/* Calculates sales per year for this year*/
ifelse(
dateDiff( {Date}, truncDate( "YYYY", now() ) ,"YYYY" ) = 0
,{TotalSales}
,0
)``````
12. Save your work by choosing Save at upper right.

If there are errors in your expression, the editor displays an error message at the bottom. Check your expression for a red squiggly line, then hover your cursor over that line to see what the error message is. Common errors include missing punctuation, missing parameters, misspellings, and invalid data types.

To avoid making changes, choose Cancel.

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's in the field.

To change the data type of a calculated field

• For Calculated fields (at left), choose the field that you want to change, then choose Change data type from the context (right-click) menu.

Unlike the other fields in the dataset, calculated fields can't be disabled. Instead, delete them.

To delete a calculated field

• For Calculated fields (at left), choose the field that you want to change, then choose Delete from the context (right-click) menu.