Two-dimensional matrix with interval values

Mon, 20 Jul 2015 21:27:19 GMT

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?

Thanks.

topic Two-dimensional matrix with interval values in QlikView

Two-dimensional matrix with interval values