2 Replies Latest reply on Feb 18, 2011 3:37 PM by rgnant_1

    Numbering items in a report, including ties

    rgnant_1

      Title

      Numbering items in a report, including ties

      Post

      Customers making qualifying purchases receive points based on the purchase.

      Required:  Report listing each customer with just the dates of the qualifying purchases and the number of points.  The report should list the customer with the most points and descend to the customer with the fewest.

      I have no problem generating this report.  What I would like to do, though, is assign a ranking.  Customer with the most points ranked 1 and so on down the line.  Customers with tied points would get the same ranking.  So the ranking could go 1,2,3,3,5 for example.

      I've tried nine million ways to do this.  I think you are hiding the 9.000.001st way, which is the one I am looking for.  

      Won't you please share?

      Thanks.

        • 1. Re: Numbering items in a report, including ties
          Sorbsbuster

          Try this script:

          #Assumes found set is correct

          #Sorts by points descending

          Sort Records [ Specified Sort Order: YourFileName::Points; descending ] [ Restore; No dialog ]

          #

          #Start at the top of the list. Set variables

          Go to Record/Request/Page [ First ]

          Set Variable [ $RankToUse; Value:1 ]

          Set Variable [ $PointsPrevious; Value:YourFileName::Points ]

          #

          #Loop through all records

          Loop

          #

          #Check if the points on this record are the same as the previous record

          If [ YourFileName::Points = $PointsPrevious ]

          #Points are the same as the previous record. Use same Rank number

          Set Field [ YourFileName::Ranking; $RankToUse ]

          Else

          #Points are NOT the same as the previous record. Use the Record number

          Set Field [ YourFileName::Ranking; Get ( RecordNumber ) ]

          #Update to the new Rank Number

          Set Variable [ $RankToUse; Value:Get ( RecordNumber ) ]

          End If

          #Capture the points for this record

          Set Variable [ $PointsPrevious; Value:YourFileName::Points ]

          #

          #Next record...

          Go to Record/Request/Page [ Next; Exit after last ]

          #

          End Loop

          #

          #Finish tidily

          Go to Record/Request/Page [ First ]

          • 2. Re: Numbering items in a report, including ties
            rgnant_1

            Thanks.  I was working on just such a script when your answer was posted.  I'll work on it more tonight.