It's been awhile since I've worked with QlikView so I apologize if this is a basic question. I have sales information I read in from a database like this:
Salesman ID
Product Group
Item
Sales
Cost
0100
BD
ABCDE
12456.00
9670.84
0100
BD
ZXCVB
3652.00
2980.03
0100
CM
12345
102587.00
86788.60
And I have a commission matrix read in from a spreadsheet like this:
GM%
Sales < $150,000
Sales $50,000 - <$100,000
Sales $100,000 - < $150,000
Sales $150,000 or more
<15%
0.50%
0.60%
0.70%
0.80%
15% - <16%
0.60%
0.70%
0.80%
0.90%
16% or more
0.70%
0.80%
0.90%
1.00%
This is an abbreviated version of the matrix table but you should get the idea. What I have to do is calculate the commission percentage by taking the salesperson's total sales (regardless of product group) to get the column across the top. Then take the salesperson's total for that product group, calculate their gross margin percentage and use that to find the row down the matrix. This example I would use salesperson 0100 total sales ($118,695) to get the column "Sales $100,00 - $150,000" across the top. Then I would have to calculate the gross margin by product group. So for Product Group BD my commission multiplier would be 0.90% ($3457.13 profit divided by $16,108 sales = 21.46%). Product group CM the commission multiplier would be 0.80% ($15,798.40 profit divided by $102,587 sales = 15.4%).
I've tried using IntervalMatch but am having problems since both axis of the matrix are ranges. I'm not sure how I can accomplish this. Any thoughts?
