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

    Finding Highest Total Value Over Multiple Records

    aboatright

      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
          aboatright

          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
            taylorsharpe

            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
              comment

              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.