Jump to content

Top N Report For a Date Range


BeckyMN
 Share

This topic is 5736 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have a call center database. There are 2 tables: CALLS & PRODUCTS. Each call that comes into the center gets a new call record. Each CALL record is related to a PRODUCT record. Each PRODUCT can be related to many CALLS but each CALL can only be related to one PRODUCT.

I have been asked to produce a report that shows the top [variable] PRODUCTS that have had the most calls occuring from [variable] thru [variable].

(the user will enter the variables when prompted)

For example; the user would want to see the 5 products that had the greatest number of calls between January 1st and March 31st this year.

I'm having trouble with the "Top N" portion of the task. Using a summary field I can get a count of the products (I'm running this from the CALLS table) but I can't get it ordered properly nor can I exclude the "bottom N"

I have a feeling that I am coming at this completely from the wrong direction. All help is appreciated.

-Becky

Link to comment
Share on other sites

A rough sketch:

In Products, 3 global fields: gStartDate, gEndDate, gN.

A new TO of Calls, named CallsInRange.

Relationship:

Products::ProductID = CallsInRange::ProductID

AND

Products::gStartDate ≤ CallsInRange::Date

AND

Products::gEndDate ≥ CallsInRange::Date

In Products, a calculation field, cCountInRange (result is number) =

Count ( CallsInRange::CallID )

Script:

Go to Layout [ "Report" (Products) ]

Show All Records

Sort Records [ Specified Sort Order: Products::cCountInRange ; descending ]

Go to Record/Request/Page [ Products::gN + 1 ]

Omit Multiple Records [ Get ( FoundCount ) - Products::gN ]

Enter Preview Mode [ Pause ]

Enter Browse Mode Go to Layout [ original layout ]

Show All Records

This needs a bit more work to handle a tie for the last place.

Link to comment
Share on other sites

I was working on an example while Comment was making a reply. His way of limiting the found set with:

Go to Record/Request/Page [ Products::gN + 1 ]

Omit Multiple Records [ Get ( FoundCount ) - Products::gN ]

is much more efficient than the way I was planning. I used a Go To Field script step as the first step, otherwise If N was the last field I was in then a change to N did not seem to always take.

ProdsCalls.zip

Link to comment
Share on other sites

This topic is 5736 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.