Calculate sum of top N records

[Former Member]

Hi guys,

how can I calculate the sum of my top n records?

If I add a group results, it shows only the sum of all records.

[abhilash_kumar · Accepted solution]

Hi Robert,

First off all, sort the records in descending order based on the database field you would like to summarize. For eg: if the report shows top 5 customers in US based on sales figures, then sort the report on sales figures in Descending order.

Next, create a formula and use this code:

WhilePrintingRecords;
numbervar inc;
numbervar top_n;
inc := inc + 1;
if inc <= 5 then //where 5 means top 5
top_n := top_n + {field_to_summarize}

Place the above formula on the Details section.

Create another formula to display the top n sums:

whileprintingrecords
numbervar top_n;

Place this formula on the Report/Page Footer.

Now, if the report is already grouped on a database field, then create a formula to reset the variables as follows:

whileprintingrecords
numbervar top_n := 0;
numbervar inc := 0;

Place the above formula in the Group Header.

You may then place the formula that displays the sum in the Group Footer.

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

thank you very much. I tried it your way and it works.

But in my case, I think it is enough to take the first two formulas you have posted.

Please correct me, but I understand that the first formula gives me the running total of the top 5 records.

The second one gives me the sum of my top 5 records.

But why do I need the 3rd formula?

[abhilash_kumar · Accepted solution]

Hi Robert,

The 1st formula gives the running total, the second one refers to the same variable for display purposes.

The 3rd formula is used to reset the variables to zero in case you wish to sum the top n records in a 'group'.

Since you're not using groups, you can choose not to use the 3rd formula; I was just giving you options!

-Abhilash

[Former Member · Accepted solution]

Perfect. It works fine!

Is it right, that the 2nd formula only shows a value, when it is placed in the report footer?

If I place it in the report header, there is no value for formula.

[abhilash_kumar · Accepted solution]

Yes, that is true. The second formula only shows the value gathered by the variable. If it is placed on the header, the variable would not contain any value as it has not gathered any.

It starts gathering the values only in the details section.

-Abhilash

[Former Member]

You can use complex formulas with variables if you want to. But, try using a Running Total instead! They calculate after Top N has processed, so you will get only the values included in the Top N in the running total, and no complex formulas are requred!

- George -