Variable Calculation based on range within Sum?

[smsherman]

Thank you in advance.

Crystal 9.

I am trying to put together a calculation that will apply different amounts to a sum based on the range.

Put another way, the first 20 items should be multiplied against x; the next 20 items should be multiplied by y; and anything beyond that should be multiplied by z.

So, for example; 42 items sold would be 20(x)+20+2(z); or
say 17 items would be 17(x); or 46 items would 20(x)+20

Right now the "items sold" is calculated based on a Sum field; and can change from month to month, person to person, etc.

I thought about using a variable scope, but am having no luck. Any insight or suggestions is appreciated! Thank you.

 

[synapsevampire]

Try posting example data and expected output, your descriptioons don't make sense, why would 42 items sold be 20(x)+20+2(z)yet 46 items would 20(x)+20 with only 40 things calculated, and no z?

And rather than speaking ot items, or other business rule language, why not say a numeric value in a row, if that's what they are?

If you want to use one calculation for the firrst 20 values of a group, then create a counter in the group header of:

whileprintingrecords;
numbervar Counter:=0;
numbervar TotalValue:=0

Then in the details use:
whileprintingrecords;
numbervar Counter:=Counter+1;
numbervar TotalValue;
If Counter < 21 then
TotalValue:=Totalcount+({table.field}*10)
else
If Counter < 41 then
TotalValue:=Totalcount+({table.field}*20)
else
If Counter < 61 then
TotalValue:=Totalcount+({table.field}*30)

Note the 10, 20, 30 changing in the formulas to reflect the different amounts used.

-k

 

[smsherman]

I apologize for the confusion, thanks for the reply!

I made a mistake in my example.

I am dealing with items sold. They can have either "returned" status, or a "sold" status. Returned status results in a negative number, "sold" results in a positive count.

Commission payments are calculated based on a tiered structure for number of sales. The first 20 sales get a base commission amount of 10$. The next 20 sales get a commission payment of $15.00 and every sale over 40 gets paid out at $20.00.

Does that make sense? Again, thank you for your time and advice.

 

[synapsevampire]

My formula should still work as a framework.

If you want more tailored results, post technical information:

example data
Expected output

-k