Performance Matrix Sales and Margin

[charlie2]

Hi,

I have a Matrix with the sales and margin's goals of the sales reps and the corresponding bonus. My idea is to show the monthly bonus a sales rep would have according to his/her YTD performance (by extrapolating sales and margin, for example). 

A simpler version of the matrix looks like that, with the x-axis containing the sales objectives and the y-axis the margins goal:

So far I did:

- Convert the matrix into a table

- Create a master element with if statements to return the bonus%. This is very inefficient plus since the matrix is way larger than this one, it turns out to be not possible, as it would need more than 100 if statements.

Any suggestion on how to calculate such KPI directly as a master element (not in the data editor)?

Thanks a lot in advance

[chrismarlow]

Hi,

I am guessing that your actual matrix cannot be expressed as a formula in the way your example can be (see below)? If it can then you should be able to do similar in Qlik.

If it cannot you can try pick/match, you may need to round your sales/margin figures somehow to feed this, but would look something like the below for your example grid;

pick(match(margin,50,52,54,56,58,60), 
	pick(match(sales,10,12,14,16,18),0,10,20,30,40),
    pick(match(sales,10,12,14,16,18),10,20,30,40,50),
    pick(match(sales,10,12,14,16,18),20,30,40,50,60),
    pick(match(sales,10,12,14,16,18),30,40,50,60,70),
    pick(match(sales,10,12,14,16,18),40,50,60,70,80), 
    pick(match(sales,10,12,14,16,18),50,60,70,80,100))

It will probably be easier to build the pick/match in Excel & copy paste in rather than try typing in via the UI (the same would go if you continued with embedded if statements, not tried it in Qlik Sense by QlikView would tolerate quite long formulae).

Cheers,

Chris.

[charlie2 · Accepted solution]

Hi @chrismarlow ,

Thanks a lot for your fast reply, highly appreciate!

The second suggestion would not work, as the bonuses are not mirrored. However, the first approach with pack and match is indeed working. I though used it a little differently, as I'm not aiming to perfectly match the value but to find it within an interval. Therefore I combined your proposal with this one: Pick Match Intervals 

All in all, that's how it turned out to work:

pick(match(-1, margin<50, margin >= 50 and margin < 52,margin >= 52 and margin <54,margin >=54 and margin <56, margin >=56 and margin <58,margin >=58 and margin <60, margin >=60), 
    pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),0,0,10,20,30,40),
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),0,0,10,20,30,40),
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),10,10,20,30,40,50),
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),20,20,30,40,50,60),
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),30,30,40,50,60,70),    
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),40,40,50,60,70,80),    
  pick(match(-1, sales <10, sales >=10 and sales > 12, sales >=12 and sales < 14,sales >=14 and sales < 16, sales >=16 and sales <18, sales >=18),50,50,60,70,80,100))

[chrismarlow]

That is a neat one, thanks for sharing back I will store that one away in the memory banks.

Cheers, Chris.