User defined function or expression?

[mmstefan]

Hi,

I'm quite new to QlikView and I'm facing currently following problem - there is a list of car dealers, for each of them I calculate let's say 3  KPIs, like "% of total sales", "% of bad loans", "% of fraud cases".

Next step  is to calculate deviation of each KPI from the average value for all the dealers. If the deviation is greater than 20%, KPI gets note=4, if > 15% then gets note=3, if > 10% than note=2 else note=1.
So before final calculation I have following variables:

total_sales_note = 3;
bad_loans_note = 2;
fraud_cases_note = 1;

Based of these 3 variables I would need to calculate so called "Dealer grading note" which has also values 1 to 4. It is calculated like following:

dealer_grading = (total_sales_note * total_sales_weight) + (bad_loans_note * bad_loans_weight) + (fraud_cases_note * fraud_cases_weight)

where total_sales_weight = 0.5, bad_loans_weight = 0.3, fraud_cases_weight = 0.2 (fixed values)

if dealer_grading > 3.2 then dealer_grading_note = 4;
else if dealer_grading > 2.6 then dealer_grading_note = 3;
else if dealer_grading > 1.45 then dealer_grading_note = 2;
else dealer_grading_note = 1;

How you would implement it in QlikView? I mean the last step, calculation of dealer_grading_note / dealer_grading. At the end, it should be shown in the textbox
I was trying to write own function, but I have still too less experience .

Thank you in advance

[Sergey_Shuklin · Accepted solution]

Hello!

You may use this sketch for picking some ideas how dealer's note can be implemented.

I've left Web View mode switched on in the attached file. You can switch it off in View panel.

[mmstefan]

Thank you!