Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Basil, you can create different expressions for each score and compare arithmetically.
Can you please post sample qvw with data ?
Basil, you can create different expressions for each score and compare arithmetically.
Can you please post sample qvw with data ?
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.
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))