Working with Values

Overview

Values are a core component of Pivot and Chart views. Values are aggregations or calculations based on the underlying data that you want to display in your views.

For example, the following pivot view shows sales per quarter (in the rows), comparing year over year (in the columns). In the pivot, the values are displayed in each cell of the pivot. In this case, the values are a sum, adding together all of the sales for the row and column:

In this example, the following donut view shows a count of incidents, grouped by priority. In the chart, the sections of the pie are proportional to the values (count of incidents):

Selecting a Value

The process for selecting values is similar for both Pivot and Chart views.

To select values, navigate to the view, and click the Select Fields button to reveal the field selection pane.

In the selection pane, there is a field that displays all the fields available on the view's table, labelled with an instruction:

Any reference fields will be marked with a right-facing arrow. Click on them to select fields from related tables:

To add the value to the chart, simply drag the desired field onto the Value fields field.

When the fields is dropped onto the Value fields field, a Field Settings dialog will appear to give further options on how to display and calculate the values.

Alternately, you can generate values using Calculated Values, which are created by formulas based on other values.

Defining Field Settings

Field Settings define how the values are calculated or aggregated for the view.

The value is defined by the following options:

• Calculation - Defines how the values are calculated. Choices are:
  • sum - Available for integer fields -- Adds all of the data within the category.
  • count rows - Counts how many rows of data within the category.
  • count distinct values - Counts how many unique values exist within the category.
  • count non-empty values - Counts how many rows of data that are not null within the category.
  • average - Available for integer fields - Averages all of the data within the category.
  • min - Available for integer fields - Returns the lowest value within the category.
  • max - Available for integer fields - Returns the highest value within the category.
  • average age - Available for date-time fields - Converts the date-time to an age (duration since the date-time), and averages all of the ages within the category.
  • min age - Available for date-time fields - Converts the date-time to an age (duration since the date-time), and returns the lowest age within the category.
  • max age - Available for date-time fields - Converts the date-time to an age (duration since the date-time), and returns the highest age within the category.
  • sum of age - Available for date-time fields - Converts the date-time to an age (duration since the date-time), and adds all of the ages within the category.
• Label - Defines the name of the value as it is displayed in the view. Should be unique from other Value fields.
• Hidden - If checked, the value will not be rendered as part of the view. Hidden values are typically used as inputs for Calculated Values.

Display As

Clicking the Display As button provides additional options for visualizing the value. Display As options do not change the value, but instead provide different ways of presenting the same data.

The additional options available are:

• difference from - Shows the difference from the value of the Base Item in the Base Field.
• % difference from - Shows the percentage difference from the value of the Base Item in the Base Field
• % of - Shows values as a percentage of the value of the Base Item in the Base Field
• running total in column - Shows the values in each column as a running total
• running total in row - Shows the values in each row as a running total
• running total in - Shows successive items in the Base Field as a running total. For example, if the Base Field is "Month", then "running total in month" will run a total of January, February, and so on. It creates a running total for every combination of other row/column fields except "Month". For example, for every product and every year there's a running total in Month.
• running total by - Show items for the each item in the Base Field as a running total. For example to have a running total showing the balance of an account use "running total by account".
• % of row - Shows the values in each row as a percentage of the total for the row
• % of column - Shows the values in each column as a percentage of the total for the column
• % of total - Shows the value in each cell as a percentage of the total for all cells
• % of total by - Shows the value in each cell as a percentage of the total by Base Field
• % running total in column - Calculates running total in column, then display it as % of column
• % running total in row - Calculates running total in row, then display it as % of row
• % running total in - Calculates running total in Base Field, then display it as % of the total in Base Field
• % running total by - Calculates running total by Base Field, then display it as % of total by Base Field