How to count the nth most frequent value 1

[ryanxr]

Hi All,

I'm using CR9 and I would like to count the number of occurrences of the nth most frequent value in a given field.

I have tried using the following two formulas:

“select top 1” formula:
if ({TableName.Field} = NthMostFrequent (1, {TableName.Field})) then
1
else
0

“sum top 1” formula:
Sum ({@select top 1})

The error that I’m getting when I try to validate the second formula is: “This field cannot be summarized.”

Any help would be appreciated.

Thanks.

 

[lbass]

You can do this with a variable. If you have no groups, then use:

whileprintingrecords;
numbervar sumnth1 := sumnth1 + {@select top 1};

Place this in the detail section. In the report footer, place the display formula:

//{@displaysumnth}
whileprintingrecords;
numbervar sumnth;

If you have groups, you would need to use a reset formula if you wanted group level totals:

//{@reset} to be placed in the group header:
whileprintingrecords;
numbervar sumnth := 0;

And to get a grand total in this case, you would change the accumulation formula to:

whileprintingrecords;
numbervar sumnth := sumnth + {@select top 1};
numbervar grtotnth := grtotnth + {@select top 1};

You would then place {@displaysumnth} in the group footer and the following formula in the report footer:

whileprintingrecords;
numbervar grtotnth;

-LB