SAP Knowledge Base Article - Public

1840442 - How to find the larger value for a mode (Most frequently occurring value) in case of a tie between 2 mode values

Symptom

There is a need to find the larger value for a mode in case of a tie between 2 mode values.

Environment

Crystal Reports 2008 V1

Reproducing the Issue

  1. Create a Crystal report based on a supported data source.
  2. Create a formula as "@Formula1" based on a field {field1} such as Mode({field1}).
  3. Place the field {field1} in Details section of the report.
  4. Place the formula "@Formula1" in Report Footer section of the report.
  5. The output of the formula would be the lower number in case of a tie between 2 mode values.

Example:

DataSet (Field1): (1,4,4,2,2,3)

@Formula1: Mode({Field1}) i.e Mode(1,4,4,2,2,3)

Output: 2 (Mode Values: 2 and 4). The lower number is displayed as output.

Cause

In Crystal Reports, Mode always shows the lower number in case of a tie between 2 mode values.

Resolution

  1. Create a formula as "@Formula1" based on a field {field1} such as NthMostFrequent(2,{field1}).
  2. Place the formula in Report Footer section of the report.
  3. The output of the formula would be the larger number in case of a tie between 2 mode values.

Example:

DataSet (Field1): (1,4,4,2,2,3)

@Formula1: NthMostFrequent(2,{field1}) i.e NthMostFrequent(2, (1,4,4,2,2,3)).

Output: 4 (Mode Values: 2 and 4). The larger number is displayed as output.

Keywords

Mode of a field, mode, larger value of mode, crystal reports, CR 2008 V1 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

Crystal Reports 2008 V1