Hi All,
I am new to Qlik and im working my way through my first project and have hit a hurdle.
I have a pivot table which has expressions that work out a rag rating for different setcions.
I have the attached table. Below are the expressions (Quite long:)
Technical:
=IF(count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Good'}>}[Technical Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>0.9
AND count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating])=0,'Good',
IF(count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Good'}>}[Technical Rating])+count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Satisfactory'}>}[Technical Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.75
AND count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.01,'Satisfactory',
IF((count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Less Than Satisfactory'}>}[Technical Rating])+count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating]))/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.35
AND count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.05,'Less Than Satisfactory',
IF((count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating])+count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Less Than Satisfactory'}>}[Technical Rating]))/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.35
OR count({<[GMRTag Flag]={'Y'},[Technical Rating]={'Unacceptable'}>}[Technical Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.05,'Unacceptable','Error'))))
Compliance:
=IF(count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Less Than Satisfactory'}>}[Compliance Rating])+count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Unacceptable'}>}[Compliance Rating])
/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.01,'Good',
IF(count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Less Than Satisfactory'}>}[Compliance Rating])+count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Unacceptable'}>}[Compliance Rating])
/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.03,'Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Less Than Satisfactory'}>}[Compliance Rating])+count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Unacceptable'}>}[Compliance Rating])
/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<0.05,'Less Than Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Less Than Satisfactory'}>}[Compliance Rating])+count({<[GMRTag Flag]={'Y'},[Compliance Rating]={'Unacceptable'}>}[Compliance Rating])
/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.05,'Unacceptable','Error'))))
Customer:
=IF(count({<[GMRTag Flag]={'Y'},[Customer Rating]={'Good'}>}[Customer Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>0.8,'Good',
IF(count({<[GMRTag Flag]={'Y'},[Customer Rating]={'Good'}>}[Customer Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>0.6,'Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Customer Rating]={'Good'}>}[Customer Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>0.5,'Less Than Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Customer Rating]={'Good'}>}[Customer Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<=0.5,'Unacceptable','Error'))))
Reserving:
=IF(count({<[GMRTag Flag]={'Y'},[Reserving Rating]={'Good'}>}[Reserving Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.95,'Good',
IF(count({<[GMRTag Flag]={'Y'},[Reserving Rating]={'Good'}>}[Reserving Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.9,'Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Reserving Rating]={'Good'}>}[Reserving Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])>=0.85,'Less Than Satisfactory',
IF(count({<[GMRTag Flag]={'Y'},[Reserving Rating]={'Good'}>}[Reserving Rating])/Count({<[GMRTag Flag]={'Y'}>}[Claim Number])<=0.85,'Unacceptable','Error'))))
These work fine, but I need to add another column for the overall score. The problem here is, I reach the expression limit as the overall score includes all of the above expressions.
I have tried putting each of these expressions into variables and using them in the formula, as below:
=IF([vComplianceRating]='Unacceptable','Unacceptable',
IF(Sum(IF([vTechnicalRating]='Unacceptable','1','0')
+IF([vComplianceRating]='Unacceptable','1','0')
+IF([vCustomerRating]='Unacceptable','1','0')
+IF([vReservingRating]='Unacceptable','1','0'))>=3,
'Unacceptable',
IF(Sum(IF([vTechnicalRating]='Unacceptable','1','0')
+IF([vComplianceRating]='Unacceptable','1','0')
+IF([vCustomerRating]='Unacceptable','1','0')
+IF([vReservingRating]='Unacceptable','1','0'))>=1,
'Less Than Satisfactory',
IF(Sum(IF([vTechnicalRating]='Unacceptable','1','0')
+IF([vComplianceRating]='Less Than Satisfactory','1','0')
+IF([vCustomerRating]='Less Than Satisfactory','1','0')
+IF([vReservingRating]='Less Than Satisfactory','1','0'))>=3,
'Satisfactory',
IF(Sum(IF([vTechnicalRating]='Unacceptable','1','0')
+IF([vComplianceRating]='Less Than Satisfactory','1','0')
+IF([vCustomerRating]='Less Than Satisfactory','1','0')
+IF([vReservingRating]='Less Than Satisfactory','1','0'))<=2,
'Good','Error')))))
But this seems to calculate the whole data set rather than the individual rows like the others do.
Any suggestions would be greatly appreciated.
