[Из песочницы] QlikView. Условное форматирование «Как в Эксель»30.03.2019 18:49

=If(Len(SubField(TextBetween(vColorFormatString,'||','||',2),'|',4))>0 AND $(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')')>0,
Pick(Match(SubField(TextBetween(vColorFormatString,'||','||',2),'|',4)
,'Больше','Меньше','Между','Равно','Первые n элементов','Первые n%','Последние n элементов','Последние n%','Выше среднего','Ниже среднего'),
//Больше
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39)  &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)') 
& '>' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Меньше
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39)  &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)')  
& '<' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Между
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)') 
& '>' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)') &' AND '&
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)') 
& '<' &  $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',6)'),
//Равно
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)') 
& '=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Первые n элементов
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'num(rank($(vELPercFormula),1))','num(rank($(vNODPercFormula),1))','num(rank($(vODPercFormula),1))','num(rank($(vKODPercFormula),1))','num(rank($(vRWAPercFormula),1))','num(rank($(vRWAPercRORWAFormula),1))','num(rank($(vRORWAPercFormula),1))') 
& '<=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Первые n%
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)')
& ' < 100-' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)')
,
//Последние n элементов
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'num(rank(-$(vELPercFormula),1))','num(rank(-$(vNODPercFormula),1))','num(rank(-$(vODPercFormula),1))','num(rank(-$(vKODPercFormula),1))','num(rank(-$(vRWAPercFormula),1))','num(rank(-$(vRWAPercRORWAFormula),1))','num(rank(-$(vRORWAPercFormula),1))')  
& '<=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Последние n%
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'$(vELPercFormula)','$(vNODPercFormula)','$(vODPercFormula)','$(vKODPercFormula)','$(vRWAPercFormula)','$(vRWAPercRORWAFormula)','$(vRORWAPercFormula)') 
& ' < ' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',2),'&chr(39)&'|'&chr(39)&',5)'),
//Выше среднего
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'round($(vELPercFormula),0.0001) > round($(vELPercTOTALFormula),0.0001)',
'round($(vNODPercFormula),0.0001) > round($(vNODPercTOTALFormula),0.0001)',
'round($(vODPercFormula),0.0001) > round($(vODPercTOTALFormula),0.0001)',
'round($(vKODPercFormula),0.0001) > round($(vKODPercTOTALFormula),0.0001)',
'round($(vRWAPercFormula),0.0001) > round($(vRWAPercTOTALFormula),0.0001)',
'round($(vRWAPercRORWAFormula),0.0001) > round($(vRWAPercRORWAFormulaTotal),0.0001)',
'round($(vRORWAPercFormula),0.0001) > round($(vRORWAPercTotalFormula),0.0001)'),
//Ниже среднего
Pick($(='WildMatch('& chr(39) &  SubField(TextBetween(vColorFormatString,'||','||',2),'|',2)&chr(39) & ','  &chr(39)&'EL %'&chr(39) &','  &chr(39)&'НОД %'&chr(39) &','  &chr(39)&'ОД %'&chr(39) &','  &chr(39)&'КОД %'&chr(39) &','& chr(39)&'RWA %'&chr(39) &','&  chr(39)&'RWA%(RORWA)'&chr(39) &','& chr(39)&'RORWA%'&chr(39) &')'),
'round($(vELPercFormula),0.0001) < round($(vELPercTOTALFormula),0.0001)',
'round($(vNODPercFormula),0.0001) < round($(vNODPercTOTALFormula),0.0001)',
'round($(vODPercFormula),0.0001) < round($(vODPercTOTALFormula),0.0001)',
'round($(vKODPercFormula),0.0001) < round($(vKODPercTOTALFormula),0.0001)',
'round($(vRWAPercFormula),0.0001) < round($(vRWAPercTOTALFormula),0.0001)',
'round($(vRWAPercRORWAFormula),0.0001) < round($(vRWAPercRORWAFormulaTotal),0.0001)',
'round($(vRORWAPercFormula),0.0001) < round($(vRORWAPercTotalFormula),0.0001)')
),'0>1') 
 

© Habrahabr.ru