Replacing an expression with a variable in pivot table

nathan4988 — Mon, 14 May 2018 09:40:10 GMT

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.

topic Replacing an expression with a variable in pivot table in App Development

Replacing an expression with a variable in pivot table