Adding calculated fields

To add a calculated field to an analysis

1. Open the QuickSight console.

2. Open the analysis that you want to change.

3. In the Data pane, choose Add at top left, and then choose + CALCULATED FIELD.

   1. In the calculations editor that opens, do the following:

   2. Enter a name for the calculated field.

   3. Enter a formula using fields from your dataset, functions, and operators.

4. When finished, choose Save.

To add or edit a calculated field for a dataset

1. Open the dataset that you want to work with. For more information, see Editing datasets.

2. On the data prep page, do one of the following:

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

   • To edit an existing calculated field, choose it from Calculated fields at left, then choose Edit from the context (right-click) menu.

   

3. In the calculation editor, 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()                                            
                                               
                                               
   )

   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.

   Add a few spaces for readability, if you like. Your expression should look like the following.

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

8. Specify the return value. For our example, the first parameter in 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 */

9. 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}                            
                                              
   )

10. 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 part of the 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                                         
    )

11. 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 any changes, choose Cancel.

To add a parameter value to a calculated field

1. You can reference parameters in calculated fields. By adding the parameter to your expression, you add the current value of that parameter.

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

3. (Optional) To manually add a parameter to the expression, type the name of the parameter. Then enclosed it in curly braces {}, and prefix it with a $, for example ${parameterName}.

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.

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.