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:

• Display Values As - This drop-down determines how the value will be displayed. Choices are:
  • normal - displays the value defined above without any additional manipulation.
  • difference from' - displays the value as a difference from another column or grouping. Additional choices are:
    • Display As Base Field - The grouping in the view to calculate the difference from.
    • Display As Base Item - The particular item within the grouping to calculate the difference from. Selecting (previous) will calculate the difference for each grouping based on the previous grouping in the view.
  • % difference from - displays the value as a % difference from another grouping. Additional choices are:
    • Display As Base Field - The grouping in the view to calculate the difference from.
    • Display As Base Item - The particular item within the grouping to calculate the difference from. Selecting (previous) will calculate the difference for each grouping based on the previous grouping in the view.
  • % of displays the value as a percent of another grouping. Additional choices are:
    • Display As Base Field - The grouping in the view to calculate the difference from.
    • Display As Base Item - The particular item within the grouping to calculate the difference from. Selecting (previous) will calculate the difference for each grouping based on the previous grouping in the view.
  • running total in column (Pivot only) displays the value as a cumulative total moving down the column.
  • running total in row (Pivot only) displays the value as a cumulative total moving across the row.
  • running total in (Pivot only) displays the value as a cumulative total within groupings. Additional choice is:
    • Display As Base Field - The grouping in the view to calculate the running total across.
  • running total by (Pivot only) displays the value as a cumulative total between groupings. Additional choice is:
    • Display As Base Field - The grouping in the view to calculate the running total across.
  • % of row (Pivot only) displays the value as a percent of the total value across the row.
  • % of column (Pivot only) displays the value as a percent of the total value within the column.
  • % of category (Chart only) displays the value as a percent of the category grouping.
  • % of secondary category (Chart only) displays the value as a percent of the secondary category grouping.
  • % of total displays the value as a percent of the sum of all of the data within the view.
  • % of total displays the value as a percent of the sum of all of the data within the grouping. Additional choice is:
    • Display As Base Field - The grouping in the view to calculate the percent of total from.
  • % of running total in column (pivot only) displays the value as a cumulative percentage within the column.
  • % of running total in row (pivot only) displays the value as a cumulative percentage within the row.
  • % of running total in (pivot only) displays the value as a cumulative percentage within the row. Additional choice is:
    • Display As Base Field - The grouping in the view to calculate the running total within.
  • % of running total by' (pivot only) displays the value as a cumulative percentage within the row. Additional choice is:
    • Display As Base Field - The grouping in the view to calculate the running total across.