About Totals and Sorting

Once you add fields and filters to your report, you might want to calculate and manipulate the data on your report by using totals or sorting.

There are three primary methods for manipulating the data in a report

  • Changing the way totals are displayed. For example, display totals as Averages.
  • Adding new fields that originate from existing fields. For example, based on the field Revenue, you create % of Revenue.
  • Creating new numbers on the fly.

Display Totals as Averages, Max, Min, etc.

Grand totals and subtotals summarize detail row or column values. You can choose to summarize the data in the following ways:

  • Sum. Useful for keeping duplicates in distinct count measures.
  • Aggregate - Default. Displayed as Total in table report. Summarizes based on the aggregator of the underlying measure.
  • Average
  • Max
  • Min

To display these, right-click the number field in the report, select Subtotals (Sums, Averages, etc) from the menu, and then select an option.

About Averages

In most cases, it looks like totals are simply summing the individual values that are visible in the report. However, it is beneficial for fields that lend themselves as averages, such as ratios. This can be seen in the following example.

Assume we want to view the Win Rate of a sales team for the entire country, as well as for each region. The user brings the field "Win Rate", "Region" and other fields into the report, and Viewpoint Analytics automatically shows the correct Win Rate across the regions (1.3%). We don't have to, and shouldn't, use the Average option for Grand Totals in this case.

The average option is appropriate for the first two number fields, and the user may use the Averages option for these two fields.

If the user would still go ahead and choose to display "Win rate" as an average they would see 23.3% which is not a meaningful number.

Display Totals that include Filtered Out Values

By default, Totals sum only the values that are visible on the report. If values are filtered out they will not be part of the total.

However, you may also want to see a total that includes the values that are filtered out. a

To display a total that includes values that are filtered out, click the Grand Total cell on the report, and select Display Total that Includes Filtered Out Values from the menu.

Display % of, Rank, and Running Sum,

To display % or, Rank, Running Sum, etc, click a field on the report and select % of, Rank, Running Sum, etc from the menu. Then, select the appropriate option.

This will create a new field in your report that behaves similar to any other fields. The differences are:

  • You may not filter based on this field.
  • You may not create another field (% or, Rank, Running Sum, setc) based on this field.

See below for further help on each option.

Display % of

When displaying values as percentages (%) you have four options:

  • Grand Total Column is 100%
  • Grand Total Row is 100%
  • Grand Total in lower right corner is 100%
  • Each <field> adds to 100%

Display Rank

When ranking values (such as Product Lines) by a number field (such as Sales Revenue), you have three options:

  • Rank across Rows
  • Rank across Columns
  • Rank Within <field>

The following examples illustrate each option.

Rank across Rows

Rank across Columns

Rank Within <field>

Note: You are always ranking by the innermost row/column ('Product Line'). You may not rank by 'Region'.

Display Running Sum

The most common use of Running Sum or Cumulative Sum is to show how a number grows over time. For example, "the total sales revenue for a year, up and through January, February, March, etc.

When creating a Running Sum field you have three options:

  • Sum across all Rows
  • Sum across all Columns
  • Break By <field>

The following examples illustrate each option.

Sum across all Rows

Sum across all Columns

Break By <field>

This option is only available when you have multiple text fields on the report.

Display % of Running Sum

The % of Running Sum option combines the % of and the Running Sum functionality. You have three options:

  • Sum across all Rows
  • Sum across all Columns
  • Break By <field>

The following examples illustrate each option.

Sum across all Rows

Sum across all Columns

Break By <field>

Sort

By default, your report is sorted in alphanumeric order by text fields (such as 'Product Line'). To re-sort a row or column, click the field on the report and select a Sort option from the menu.

The image below illustrates which fields can be re-sorted based on their placement on the report.

Creating New Measures

Viewpoint Analytics allows you to create three types of new measures directly within a report:

  • % of Rank, Running Sum, etc.
  • Calculated Measures
  • Trend Measures
Right-click in the measure field, select User Defined Measure, and then select an option. For details on creating a calculated measure, see Create a Calculated Measure.