Formula for Sales Commission

[blueboyz]

I'm using Crystal Reports 2008 and Peachtree 2011(Pervasive SQL is the database engine).

I am creating a sales commission report.
The sales rep has a field for storing the commission category. The categories are STR, SAE and SAP.

If the sales rep's commission category is "STR" then there are 5 different commission rates to use:
If the item sold falls into price level 1, then the commission rate is 5%, if the item sold falls into price level 2, then the commission rate is 4%, if the item sold falls into price level 3, then the commission rate is 3%, if the item sold falls into price level 4, then the commission rate is 2%, and if the item sold falls into price level 5, then the commission rate is 1%.

If the sales rep's commission category is "SAE", the commission rates are: PriceLevel1 = 17% commission rate, PriceLevel2 = 15%, PriceLevel3: 12%, PriceLevel4: 10%, PriceLevel 5: 8%, PriceLevel 6: 6% and PriceLevel 7: 4%

If the sales rep's commission category is "SAP", the commission rates are: PriceLevel1 = 20%, PriceLevel2 = 17%, PriceLevel3 = 14%, PriceLevel4 = 12%, PriceLevel5 = 10%, PriceLevel6 = 8% and PriceLevel7 = 6%

I tried the following formula:
If{Employee.Type}= "STR" then (({LineItem.PriceLevel1Amount}*.05) or ({LineItem.PriceLevel2Amount}*.04) or ({LineItem.PriceLevel3Amount}*.03) or ({LineItem.PriceLevel4Amount} * .02) or {LineItem.PriceLevel5Amount} * .01))

I receive and error message that "the remaining text does not appear to be part of the formula"

Can someone help me with the formula so the correct commission rates are applied according to the commission category and Price Level?

 

[Turkbear]

Check your parens ( the one in red is missing from your posting):

If{Employee.Type}= "STR" 
then 
(
({LineItem.PriceLevel1Amount}*.05) 
or
({LineItem.PriceLevel2Amount}*.04) 
or 
({LineItem.PriceLevel3Amount}*.03) 
or 
({LineItem.PriceLevel4Amount} * .02) 
or 
[COLOR=red]([/color]{LineItem.PriceLevel5Amount} * .01)
)

BUT
even with that corrected, what do you expect this formula to show, since there is no determinant to resolve the OR tests?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[blueboyz]

Turkbear:

I inserted the Parenthesis as you noted above. I now get an error message that a Boolean is required here and (({LineItem.PriceLevel1Amount}*.05) is highlighted.

I know the formula isn't complete, but I'm not sure how to create the formula.

If my data is like:
SalesRep1: {Employee.EmployeeType} = "STR"
Sales for SalesRep1:
Item ID Unit Price Amount
A .48 .96
B .67 1.34

where the unit price .48 falls into PriceLevel3 and the unit price .67 falls into PriceLevel4

then I want crystal to show commission as:
Item ID Unit Price Amount Commission
A .48 .96 .03
B .67 1.34 .03

The commission is figured that since .48 falls into PriceLevel3, we use 3% as the commission rate (.96 x 3% = .0288 or .03) and .67 falls into PriceLevel4, we use 2% as the commission rate (1.34 x 2% = .0268 or .03).

Here are the tables I have for selecting the correct commission rate:
If the commission type {Employee.EmployeeType} = "STR" then
multiply the Amount by the appropriate Commission Rate.
Commission Rates for "STR":

Price Level Commission Rate
1 5%
2 4%
3 3%
4 2%
5 1%

If the commission type {Employee.EmployeeType} = "SAE" use the following commission rates:
Price Level Commission Rate
1 17%
2 15%
3 12%
4 10%
5 8%
6 6%
7 4%

If the commission type {Employee.EmployeeType} = "SAP", use the following commission rates:
Price Level Commission Rate
1 20%
2 17%
3 14%
4 12%
5 10%
6 8%
7 6%

I have another formula that determines what Price Level the Unit Price falls into. The report is grouped by Sales Rep and then by the Price Level so I can get a total for the sales in each Price Level.

My PriceLevel formula is:
If not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} >= {LineItem.PriceLevel1Amount})then "Price Level 1"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel1Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel2Amount}) then "Price Level 2"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel2Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel3Amount}) then "Price Level 3"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel3Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel4Amount}) then "Price Level 4"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel4Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel5Amount}) then "Price Level 5"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel5Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel6Amount}) then "Price Level 6"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel6Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel7Amount}) then "Price Level 7"
else if not({LineItem.ItemID} in ["POINTS", "GNACWPS"]) and ({JrnlRow.UnitCost} <= {LineItem.PriceLevel7Amount} and {JrnlRow.UnitCost} >= {LineItem.PriceLevel8Amount}) then "Price Level 8"
else if {LineItem.ItemID} = "POINTS" then "Price Level 9"
else if {LineItem.ItemID} = "GNACWPS" then "Price Level 10"

The difficult part for me is how to apply the correct commission rate depending on the sales rep's commission category (STR, SAE or SAP).

Any help is greatly appreciated.

 

[blueboyz]

I created 4 formulas that I think will work:
1) {@COMMISSIONSTR} =
If({Employee.Type}= "STR" and {@Price Levels} = "Price Level 1") then ({JrnlRow.AmountOrdered} * .05)
else If({Employee.Type}= "STR" and {@Price Levels} = "Price Level 2") then ({JrnlRow.AmountOrdered} * .04)
else If({Employee.Type}= "STR" and {@Price Levels} = "Price Level 3") then ({JrnlRow.AmountOrdered} * .03)
else If({Employee.Type}= "STR" and {@Price Levels} = "Price Level 4") then ({JrnlRow.AmountOrdered} * .02)
else If({Employee.Type}= "STR" and {@Price Levels} = "Price Level 5") then ({JrnlRow.AmountOrdered} * .01)

2) {@COMMISSIONSAE} =
If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 1") then ({JrnlRow.AmountOrdered} * .17)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 2") then ({JrnlRow.AmountOrdered} * .15)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 3") then ({JrnlRow.AmountOrdered} * .12)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 4") then ({JrnlRow.AmountOrdered} * .10)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 5") then ({JrnlRow.AmountOrdered} * .08)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 6") then ({JrnlRow.AmountOrdered} * .06)
else If({Employee.Type}= "SAE" and {@Price Levels} = "Price Level 7") then ({JrnlRow.AmountOrdered} * .04)

3) {@COMMISSIONSAP} =
If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 1") then ({JrnlRow.AmountOrdered} * .20)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 2") then ({JrnlRow.AmountOrdered} * .17)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 3") then ({JrnlRow.AmountOrdered} * .14)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 4") then ({JrnlRow.AmountOrdered} * .12)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 5") then ({JrnlRow.AmountOrdered} * .10)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 6") then ({JrnlRow.AmountOrdered} * .08)
else If({Employee.Type}= "SAP" and {@Price Levels} = "Price Level 7") then ({JrnlRow.AmountOrdered} * .06)

4) {@COMMISSION} =
IF {Employee.Type} = "STR" THEN {@COMMISSIONSTR}
ELSE IF {Employee.Type} = "SAE" THEN {@COMMISSIONSAE}
ELSE IF {Employee.Type} = "SAP" THEN {@COMMISSIONSAP}

I placed {@COMMISSION} on the report and it appears to be working correctly.