Some advanced matching in Qlikview

[#1]

Dear Qlikviewers,

May you please help me on the following problem:

I need to match some value in percent, say A to some another percent value, B and return third value, C which linear dependent on value B

this was previously done easy by Excel function TREND, with the following formula:

Max_Score = 100%

Median_Score = 97%

Minimum_Score = 85%

Received_Score = 98.25% (for example)

so, in Excel Result=IF(Received_Score>Minimum_Score,IF(Received_Score<=Median_Score,TREND({0.9,0},Median_Score:Minimum_Score,Received_Score),TREND({1,0.9},Maximum_Score:Median_Score,Received_Score)),0) and Result is 0.9416

Obviously we divided our range on two parts: when Received_Score is higher then Median_Score we match with one range, when Received_Score is lower then Median_Score - with another range.

The problem is that I need to have one correspondence between Received_Score and Result based on several conditions.

I need this to be done as expression, as I have Received_Score as expression result; Max_Score, Median_Score, Minimum_Score and trends could be determined in the script.

Appreciate your help,

Regards, Basil

[#2 · Accepted solution]

Basil, you can create different expressions for each score and compare arithmetically.

Can you please post sample qvw with data ?

[#3]

Dear dathu.qv,

Please find my original report and sample xls file with what I need to calculate in QV.

Please find QV report tab "Indicators" with several speedometers, this is my team KPI's.

Based on this KPI's I need to calculate team performance factor. Previously it was done in Excel, now I need to get rid of Excel and use QV for reporting.

[#4]

It was finally done arithmetically, with rather large formula

If(If((1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))<0.97,7.5*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-6.375,3.33*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-2.33)*0.25+If((1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.97,7.5*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-6.375,3.33*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-2.33)*0.1+If(((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))<0.8,18*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))-13.5,0.5*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))+0.5)*0.1+If((1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.8,18*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-13.5,0.5*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))+0.5)*0.05+If(Avg([Mars Survey Score])<4.2,2.25*Avg([Mars Survey Score])-8.55,0.125*Avg([Mars Survey Score])+0.375)*0.2+If(BacklogDays>1,-0.9*BacklogDays+1.8,-0.1*BacklogDays+1)*0.2+If(Esc<1,-0.9*Esc+2.7,-0.05*Esc+1)*0.1<0.8,If((1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))<0.97,7.5*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-6.375,3.33*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-2.33)*0.25+If((1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.97,7.5*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-6.375,3.33*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-2.33)*0.1+If(((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))<0.8,18*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))-13.5,0.5*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/

(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))+0.5)*0.1+If((1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.8,18*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-13.5,0.5*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))+0.5)*0.05+If(Avg([Mars Survey Score])<4.2,2.25*Avg([Mars Survey Score])-8.55,0.125*Avg([Mars Survey Score])+0.375)*0.2+If(BacklogDays>1,-0.9*BacklogDays+1.8,-0.1*BacklogDays+1)*0.2+If(Esc<1,-0.9*Esc+2.7,-0.05*Esc+1)*0.1,If(If((1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))<0.97,7.5*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-6.375,3.33*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-2.33)*0.25+If((1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.97,7.5*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-6.375,3.33*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-2.33)*0.1+If(((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))<0.8,18*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))-13.5,0.5*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))+0.5)*0.1+If((1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.8,18*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-13.5,0.5*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))+0.5)*0.05+If(Avg([Mars Survey Score])<4.2,2.25*Avg([Mars Survey Score])-8.55,0.125*Avg([Mars Survey Score])+0.375)*0.2+If(BacklogDays>1,-0.9*BacklogDays+1.8,-0.1*BacklogDays+1)*0.2+If(Esc<1,-0.9*Esc+2.7,-0.05*Esc+1)*0.1<0.9,If((1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))<0.97,7.5*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-6.375,3.33*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-2.33)*0.25+If((1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.97,7.5*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-6.375,3.33*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-2.33)*0.1+If(((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+

Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))<0.8,18*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))-13.5,0.5*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))+0.5)*0.1+If((1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.8,18*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-13.5,0.5*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))+0.5)*0.05+If(Avg([Mars Survey Score])<4.2,2.25*Avg([Mars Survey Score])-8.55,0.125*Avg([Mars Survey Score])+0.375)*0.2+If(BacklogDays>1,-0.9*BacklogDays+1.8,-0.1*BacklogDays+1)*0.2+If(Esc<1,-0.9*Esc+2.7,-0.05*Esc+1)*0.1+0.1,If((1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))<0.97,7.5*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-6.375,3.33*(1-Count(If(Agent=Service_Agent,[Mars Closed SLA Breached Incident]))/Count(If(Agent=Service_Agent,[Mars Closed Incident])))-2.33)*0.25+If((1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.97,7.5*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-6.375,3.33*(1-Count(if(Agent=Service_Agent,[Mars TQ Flag]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-2.33)*0.1+If(((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))<0.8,18*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))-13.5,0.5*((count(if((Agent=Service_Agent)and([Mars Closed Incident Solution Attached]='True'),[Mars Closed Incident]))+Count(if((Agent=Service_Agent)and([Mars Opened Incident Solution Attached]='True'),[Mars Opened Incident])))/(count(if(Agent=Service_Agent, [Mars Closed Incident]))+count(if(Agent=Service_Agent, [Mars Opened Incident]))))+0.5)*0.1+If((1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))<0.8,18*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))-13.5,0.5*(1-Count(If(Service_Agent=Agent,[Mars Closed CI Breached Incident]))/count(if(Agent=Service_Agent, [Mars Closed Incident])))+0.5)*0.05+If(Avg([Mars Survey Score])<4.2,2.25*Avg([Mars Survey Score])-8.55,0.125*Avg([Mars Survey Score])+0.375)*0.2+If(BacklogDays>1,-0.9*BacklogDays+1.8,-0.1*BacklogDays+1)*0.2+If(Esc<1,-0.9*Esc+2.7,-0.05*Esc+1)*0.1+0.0999))