0 Replies Latest reply: May 14, 2018 5:40 AM by Nathan Hutchcraft RSS

    Replacing an expression with a variable in pivot table

    Nathan Hutchcraft

      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.