3 Replies Latest reply on Dec 14, 2012 9:03 AM by comment

# Finding Highest Total Value Over Multiple Records

I didn't know what to name this in the subject line. This is my last day before vacation and my brain must already be in vacation mode because I am stumped on what seems to be a simple solution.

I have 499,375 records representing water bills. I was asked to find the top 5 total users over the course of a year.

The relevant fields I'm working with are:

Customer_ID - Unique identifier for a customer

Transaction_Date - The date of the water bill. Most customers will have 12 bills in my found set.

Usage - The amount of water used during a billing cycle.

I started off by making a self relationship with Customer_ID

Next I had a calculation field (Usage_REL) that was the Usage from the related table. So far so good, right?

I then created a summary field (sUsage_REL) to total Usage_REL.

Using the fields in a summary part sorted by Customer_ID will give me the individual totals for each customer, but we're talking over 40 thousand customers. I can't sort by usage since the summary layout part is by Customer_ID

I'm just going round and round trying to get this answer. What am I missing?

• ###### 1. Re: Finding Highest Total Value Over Multiple Records

Ok my brain started working again.

Solution:

1. Self relationship using primary key (Customer_ID) <--index it too

2. Create summary field to total usage. (sUsage)

3. Create a calculation field (Usage_REL) that = sUsage from the related table.

4. On your layout, use the Usage_REL field from the related table

Message was edited by: aboatright

• ###### 2. Re: Finding Highest Total Value Over Multiple Records

In the Customer TO, have a relationship to the Usage TO, which I'm sure you already have.

In the Usage TO, create a field "Usage for 2012" or something like that.  Set it equal to:  If ( Year ( Usage Date ) = 2012; Quantity ).

In the Customer TO, have a field called something like "Quantity for 2012" and make it a calculation equal to Sum ( Usage TO::Usage for 2012 ).

Then you go to a Customer TO layout and place the "Quantity for 2012" field any where you want and it'll show you the 2012 quantity for each customer.

• ###### 3. Re: Finding Highest Total Value Over Multiple Records

aboatright wrote:

I can't sort by usage since the summary layout part is by Customer_ID

That's not entirely correct. If you have a summary field defined as Total of Usage, you can sort by CustomerID, descending + Reorder based on summary field [sTotalUsage].

Using a layout with only a sub-summary by CustomerID part, this will display a list of customers ordered by their usage. If you like, you can  loop until you get to the 6th group and omit the records from there - thus leaving only the top 5 customers' records in the found set.

No relationship is required for this to work - you only need to find the records of the relevant year and sort them. Note that a self-join relationship based on matching CustomerID alone will include the customer's records from all years.

Note also that sorting and reordering half a million records might take a while.