3 Replies Latest reply on Dec 18, 2014 3:28 AM by user19752

# Selecting value ranges from a record count

I´m working on two rewards programs for my employees.

One of them awards points by level. Level is determined by the number of transactions made by employee.

For example:

If employee made 22 transactions, he gets:

3 points for each of first 5 transactions (15 point total)

4 points for each of the following 5 transactions - 6 to 10 - (20 points total)

5 points for each of the following 5 transactions - 11 to 15 - (25 points total)

6 points for each transaction above 15. (42 points in this example)

Employee gets in total 92 points.

In the other program, employees get a reward (gift) for every 5 transactions.

In this case i need to keep track of gifts redeemed.

I´d appreciate any help.

• ###### 1. Re: Selecting value ranges from a record count

Try

Let ( [

c = 22 ;

times3 = Min ( c ; 5 ) ;

times4 = Min ( c - times3 ; 5 ) ;

times5 = Min ( c - times3 - times4 ; 5 ) ;

times6 = Max ( 0 ; c - 15 )

] ;

times3 * 3 + times4 * 4 + times5 * 5 + times6 * 6

)

Manuel Campa Merino wrote:

If employee made 22 transactions, he gets:

…

Employee gets in total 92 points.

Actually, it's 102 …

• ###### 2. Re: Selecting value ranges from a record count

Oops, it was late; this one's simpler:

Let ( [

c = 22 ;

times3 = Min ( c ; 5 ) ;

times4 = Min ( c - 5 ; 5 ) ;

times5 = Min ( c - 10 ; 5 ) ;

times6 = Max ( 0 ; c - 15 )

] ;

times3 * 3 + times4 * 4 + times5 * 5 + times6 * 6

)

• ###### 3. Re: Selecting value ranges from a record count

Another thinking

Let ( [

c = 22 ;

times3 = c * 3 ; //every transactions get 3 points at least

times4 = Max ( c - 5 ; 0 ) ; //every transactions excluding first 5 get 1point bonus

times5 = Max ( c - 10 ; 0 ) ; //same as excluding first 10

times6 = Max ( c - 15 ; 0 )

] ;

times3 + times4 + times5 + times6

)