Im stuck!! hope someone can help.

 

This is what i need to achieve;

 

We have customers who purchase 2 different products (product A & product B).

We are running a points promotion on these 2 products for July / Aug / Sept/

 

If a customer purchases Product A, they get 1 point. if they purchase product B, they get 2 points.

 

Thats easy enough.

 

Simply;

 

GROUP 1 =     CUSTOMER /JULY TOTAL /JULY  PRD A /JULY PRD B/POINTS A/POINTS B/TOTAL POINTS

 

Lets say the customers July total was 95 units, with 35 units being prdt A and 60 units being prdt B. 

 

his points would be 35+120 = total points 155.

 

 

 

 Management want points accumulated ONLY on unit sales ABOVE the customer average monthly sales from Jan > Jun.

 

So if the customers average was 50 units per month for the first 6 months, then he would only accumulate points on 45 units.  (July's 95 units  - avg 50 units)

 

My problem now is, how do I know how many points to allocate to the 45 units over the average threshold?

 

I have tried;

 

GROUP 1 =     CUSTOMER /JULY TOTAL /JULY  PRD A /JULY PRD B

 

Then in DETAILS, have the INVOICE DATE, with the UNIT Quantity, and a Running Total.

I get/understand this bit, and I can see when the RT exceeds 50 units.

 

GH1 =         CUSTOMER A / JULY=95 / PRD A=35 / PRD B=60

 

Details =     01/07/2012     PRD A        5 unit       RT = 5           

                   03/07/2012     PRD B        7 unit       RT = 12

                   05/07/2012     PRD A      10 unit       RT = 22

                   09/07/2012     PRD B        4 unit       RT = 26

                   12/07/2012     PRD B        4 unit       RT = 30

                   12/07/2012     PRD B      19 unit       RT = 49

                   14/07/2012     PRD B        1 unit       RT = 50    <<< HERE

                   14/07/2012     PRD B      15 unit       RT = 65

                   17/07/2012     PRD A      10 unit       RT = 75

                   18/07/2012     PRD A      10 unit       RT = 85    

                   22/07/2012     PRD B        6 unit       RT = 91    

                   24/07/2012     PRD B        4 unit       RT = 95           

I then wrote another formula like;  (which I was hoping to sum by customer and *2)

 

 

BUT, I cannot then summarize this formula by customer as I am using a summarized field {RT} in the formula.

 

 

:|

 

Can anyone provide some assistance? or an easier method to achieve what i want to achieve?

 

Thanks for your help.

 

Mike.

 

 

Yes, it helps slightly. I thought thats the route I would need to go down, but honestly, Im a right novice with shared variables, so im sort of in the same situation. I dont know where to start or even know what to 'share'

 

what field / value would I declare as a shared variable? Can you sum shared variables?

 

Do you know of any threads i could look at for reference?

here's the summary: shared variables typically come in groups of 3, increment, display, reset.

 

reset, typically in a group header

shared numbervar x;

x := 0;

""  // will hide the zero on your report

 

display, typically in a group footer

shared numbervar x;

x

 

increment, typically in the detail section

shared numbervar x;

 

if {table.field} = something then

  x := x + {table.field1}

else

  x := x + {table.field2};

 

"" //again hides the value of x on the report

 

= is used to test for values being equal

:= is used to assign a value to a variable.

 

the increment uses an 'if' as i am assuming that you want to control what values are used.  The if part can be as simple or as complex as you want and doesn't need the else...it's all up to you and how/what the report requirements are.

 

HTH...some more

 

I think im going to give up on this one :(

 

Cant seem to get the results I want. Cant work out the logic for it :(

This is what I have and what I want to achieve:

http://img152.imageshack.us/img152/5163/93035810.jpg