topic Re: Performance Matrix Sales and Margin in App Development

Performance Matrix Sales and Margin

charlie2 — Mon, 07 Jun 2021 13:31:01 GMT

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

Re: Performance Matrix Sales and Margin

chrismarlow — Mon, 07 Jun 2021 17:07:59 GMT

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.

Re: Performance Matrix Sales and Margin

charlie2 — Tue, 08 Jun 2021 08:58:29 GMT

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))

Re: Performance Matrix Sales and Margin

chrismarlow — Tue, 08 Jun 2021 09:02:25 GMT

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

Cheers, Chris.