Creating a Top 5 summary

[Dean Redding]

Hi All,

I have created a report showing me the total sales from my customers per year.

What I am trying to see now is only my top 5 customers for the year.

Currently it is showing me all the customers that have dealt with me for the year.

How do I limit it to showing me only the top 5 of the total sales per customer?

Each page represents a year and the top 5 for the year.

I am 98% there......

Thanks in advance...

Dean

At the moment it looks like this:

2008

1. ACME Company $10,0000

2. ABC Company $ 9,0000

3. Bob The Builder $ 8,500

4. Jackie Jones $ 5,000

5. 123 Company $ 4,000

6. XYZ Company $ 3,500

7. Smith The Plumber $ 3,000

8. John Doe $ 2,800

9. Bob Citizen $ 2,500

10. Peter Smith $ 2,000

11. Jason Green $ 1,500

12. Mike Brown $ 1,000

13. Robert White $ 500

14. Sarah Jane $ 300

15. Kelly White $ 200

I want it to look like this:

2008

1. ACME Company $10,0000

2. ABC Company $ 9,0000

3. Bob The Builder $ 8,500

4. Jackie Jones $ 5,000

5. 123 Company $ 4,000

Edited January 12, 2009 by Guest

[Ocean West]

One method...

Create a layout that only has the primary key for the customer create a script that sorts the records by order of sales, go to that layout copy all records,

Go to Layout ["CUSTOMERID"]

Perform Find [Restore]

Sort Records [Restore; No dialog]

Copy All Records/Requests

Go to Layout ["DATA ENTRY"]

Paste [select; customer::globalID]

then insert more steps to goto related records by the items in the global id field

thus you have isolated your top five customers.

[comment]

How about:

Show All Records

Sort

Go to Record #6

Omit Multiple [ Get (FoundCount) - 5 ]

[Ocean West]

elegant and simple as usual...

It wasn't stated in the original post but I suspect that this may be a sub summarized report.

so either method would only result in the first five records of the top client.

[comment]

You can do the same thing in conjunction with Edoshin's Fast Summaries method. Roughly:

Loop

Exit if you're on the last record

Jump 5 records ahead

Omit Multiple [ GetSummary ( sCount ; Category ) - 5 ]

End loop

[Søren Dyhr]

It wasn't stated in the original post but I suspect that this may be a sub summarized report.

So did I originally, this seems a little tricky to me that you first sort in groupings and then find those groups where the summary is among the high five, can this be done without storing anything temporarily???

--sd

[Dean Redding]

Thank you for your kind answers, but I did forget to mention that the report is a sub summarized report.

At the moment the individual records show the individual sales of the clients.

When I do a sub summary of that clients sale.

But I am getting all of my clients, but only want to see my top 5 leading customers for each year.

So far in the script I have it sorted by year and then customer.

Then in the report I have sub summarized it by Year and the customer.

It is showing me exactly what I want, but I have for example in 2008, a list of 50 customers totals, I only want the 5 showing.

Sorry for not mentioning that at the beginning.

[Ocean West]

well you could to Goto Related record with the check box using all records from found set then use Comment's technique.

Goto Related Record Matching all in found set

Sort thru the relationship sort it by a field that is a sum or total of sales.

Go to Record #6

Omit Multiple [ Get (FoundCount) - 5 ]

Create a layout from the context of the client for printing omitting all but the first five.

[comment]

Uhm... in case it wasn't clear, my last post addresses the issue.

[Søren Dyhr]

Uhm... in case it wasn't clear, my last post addresses the issue.

Well I for one do not get it, using Edoshins method the way you descripe it only gives the five first of each category if they're available, how does it relate to volume of the sales - the count merely deals with the number of records. Shouldn't a single customer with a whopping purchase that beats everthing else that year go straight to the top of the list, is it more loyal to make many small handouts during the year?

http://en.wikipedia.org/wiki/Pareto_principle

--sd

[comment]

it only gives the five first of each category if they're available, how does it relate to volume of the sales

I am not sure I understand the difficulty you point to. The first five of each category are determined by the sort order. So it's just a matter of sorting correctly. Here's a more detailed description of the algorithm:

Find the records you want to report on;

Sort by Year (ascending), Customer (descending, re-order by TotalOfSales);

Go to first record;

Loop:

Exit loop when you get to the last record in found set;

If Year ≠ $year, set $year to Year and $i to 1; else set $i to =+1;

If $i > 5, omit the customer's records; else skip to next customer;

End loop

[comment]

P.S. If there are many sales to small customers, you could economize this by remembering how many records are there in the entire year, and how many have been skipped already. So when you get to customer #6, you can omit all the remaining records in the current year at once.

[Søren Dyhr]

Indeed - I have forgotten about or ignored the feature shown in the image above.

I've too attached my fooling around with the technique! But one thing bothers me slightly, the total of sales is only the figure among the top 5, the years sale needs to get stored in global before attempting to strain... or?

--sd

TopFive.zip

Edited January 13, 2009 by Guest

[comment]

Yes, the totals are irrelevant here, since they reflect the found set. I have purposefully limited myself to manipulating the found set only. This way you can produce the same report for certain products only, for example, just by finding them first.

But it should be possible to do the same thing with relationships - provided that the find criteria can be translated to predicates. Then you'd have access to the unfiltered totals (or differently-filtered totals).

[Dean Redding]

Thanks for the discussion everyone.

Top make it easier for everyone, cause I am getting confused by all these different scripts...

I have attached the current report, the script I made & the layout settings for the report.

In the report view, you will see my sales for 2001 & 2002.

For example, Mark Corbett did $2850.00 dollars in 2002. He was my no 1 customer at the time.

The $2850.00 is a summary of 19 records.

I cannot omit records cause that will remove the individual sales and not give the correct data.

The report attached is exactly what I want, but I only want to see the top 5 highest sales.

See the attached pictures and see if I am doing anything wrong or if you need more screen shots, I will post them too.

Thanks,

Dean

[Dean Redding]

it's not letting me attached the PDF

You can get it from my website...

http://public.me.com/deanredding

[Søren Dyhr]

Dean you can delete the above messages, as well as reclaim storage place on you virtual drive...

--sd

Screenshots.pdf

[Dean Redding]

So how did you go?

Is there a solution that can help?

and based on my screen shots, can I get it to be the Top 5 customers?

By the way, Im the one who asked the original question at the top of this post.

[Søren Dyhr]

Is there a solution that can help?

Now where we have established how to make a top 5 summary, is it required to put the result into two aligned global fields:

http://www.kevinfrank.com/download/kf-fast-summary.zip

Is it enough to go on??

--sd

[comment]

two aligned global fields

I believe those are actually printing columns.

[Søren Dyhr]

I cannot omit records cause that will remove the individual sales and not give the correct data.

Are you sure? Here grouped by year ... as suggested by Comment!

--sd

TopFiveMod.zip

[comment]

as suggested by Comment!

Not quite. You are making it too easy for yourself by having inadequate data (this is the reason why I haven't posted a demo - I am too lazy to generate the necessary test data). You should have records spanning multiple years, and enough of them to make a difference between a summary report (by year/customer) and a report of top 5 customers in each year.

[Dean Redding]

Thank you for your attached file, it seems to be what I am after.

I will look into your file and convert it to my database...

thank you for your help, I really appreciate it.

[Søren Dyhr]

You should have records spanning multiple years, and enough of them to make a difference between a summary report

Why? Comments theorem, is not the same as Fermat's dito! Where might it be that straw breaking the back of the kamel?

--sd

[comment]

See for yourself.

TopFiveMod.fp7.zip

[Søren Dyhr]

I can see what I'm ignoring thanks for pointing it out, top fiving should be looped to deal with all occurences of breakers.

--sd

[comment]

Here's what I meant.

TopCustomersByYear.fp7.zip

[Søren Dyhr]

:thumbup:

--sd