mBlaze Mar 30 2019 at 15:58

QlikView. Условное форматирование «Как в Эксель»

19 min

6.2K

Задача – сделать условное форматирование таблицы «как в эксель». QlikView вполне себе справляется со стандартными задачами раскраски по условию, но вот с выбором параметров динамически, да еще и как в эксель – возник вопрос «это как вообще..?».
Итог: было несколько вариантов реализации, несколько версий, потрачено немалое время на тестирование и отладку. Проводился рефакторинг уже готовой реализации. Представляю итоговый вариант, удовлетворяющий потребности заказчика, оттестированный и выверенный.

Функциональная задача(постановка)

Имеется таблица, в которой измерения и выражения выбираются динамически, формулы расчета самих значений просты: суммы, средние, относительные проценты, абсолютные значения и тому подобное.

Требуется дать возможность пользователю раскрасить таблицу в зависимости от выбранных параметров. Ключевые вводные — один столбец должен уметь раскрашиваться разными цветами, то есть мульти-раскраска, по разным параметрам. Второе ключевое – должна быть возможность раскрасить поле в зависимости от другого «взаимосвязанные параметры», например, мы раскрашиваем «сумму покупок» в зависимости от значения «средняя цена продуктов на рынке».
Далее приведу основную часть реализации. Я не буду останавливаться на таких моментах, как скрыть/закрыть меню, создание списков и тому подобное. Если появятся вопросы по деталям расскажу дополнительно.

Итоговый результат, путь к которому был тернист:

Блок 1. Подготовка данных для функционального меню

Код готовит фундамент для списков меню описанного в блоке №3

tColorsFormatListSelection:
LOAD * INLINE
[%ColorDisplayNameFormat
Больше
Меньше
Между
Равно
Первые n элементов
Первые n%
Последние n элементов
Последние n%
Выше среднего
Ниже среднего
];
tFilterTypeListSelection:
LOAD * INLINE
[%FilterDisplayNameType
Больше
Меньше
Равно
];
tColorsTableList:
LOAD*INLINE
[%ColorDisplayName|%ColorRGBValue|%SortColorList
Темно-красный| RGB(192,0,0)|1
Красный| RGB(255,0,0)|2
Оранжевый| RGB(255,192,0)|3
Желтый| RGB(255,255,0)|4
Светло-зеленый| RGB(146,208,80)|5
Зеленый| RGB(0,176,80)|6
Светло-синий| RGB(0,176,240)|7
Синий| RGB(0,112,192)|8
Темно-синий| RGB(0,32,96)|9
Лиловый| RGB(112,48,160)|10
](delimiter is '|');

tColorFieldsListSelectionRelation:
Load
  [Поле Qlik] AS %ColorFieldName,
  [Измерение/Значение] AS %ExpressionType
D:\QLIK\Data\Mapped_fields.xlsx
(ooxml, embedded labels, table is [MetaField])
WHERE Match([Измерение/Значение],'Измерение','Значение');
tColorFieldsListSelectionClause:
Load
  [Поле Qlik] AS %ColorFieldNameClause,
  [Измерение/Значение] AS %ExpressionTypeClause
FROM
D:\QLIK\Data\Mapped_fields.xlsx
(ooxml, embedded labels, table is [MetaField])
WHERE Match([Измерение/Значение],'Значение');

Так же мне потребовалось создать несколько десятков переменных:

vColorFormatString – собранная строка

vColorFormatString1…10 – предназначены для разбора строки из vColorFormatString на 10 параметров для абсолютных значений, именно такое ограничение я сделал намерено, моё мнение, что больше не нужно, в целом это ограничение чисто идеологическое и потратив не много времени количество параметров можно увеличить.

vColorFormatStringPerc1…10 – предназначены для разбора строки из vColorFormatString на 10 параметров для относительных значений(проценты)

vColorFormat1…10 — цвета

Блок 2. Разработка функционального меню1

Назначение:

Отображает текущий список созданных параметров пользователем
Возможность удалить какой-либо параметр условного форматирования
Вызывает меню выбора параметров условного форматирования

Остановимся подробнее на двух элементах ниже.

Первый — это отображение текущих созданных параметров.

Данная область это текстовые объекты, отображающие текущие созданные параметры на основе парсинга строки. А вот теперь первый кирпичик этого всего действа:

Формула отображения текста

=
SubField(TextBetween(vColorFormatString,'||','||',1),'|',1) & '' & If(SubField(TextBetween(vColorFormatString,'||','||',1),'|',1) = SubField(TextBetween(vColorFormatString,'||','||',1),'|',2),'',
SubField(TextBetween(vColorFormatString,'||','||',1),'|',2)) & ' ('&
 
SubField(TextBetween(vColorFormatString,'||','||',1),'|',4) & ' '& 
If( Index(SubField(TextBetween(vColorFormatString,'||','||',1),'|',2),'%')>0 OR Index(SubField(TextBetween(vColorFormatString,'||','||',1),'|',4),'%')>0,
If(WildMatch(
SubField(TextBetween(vColorFormatString,'||','||',1),'|',4),'*Среднего*'),'',
num($(=SubField(TextBetween(vColorFormatString,'||','||',1),'|',5)),'# ##0,00')) & ' % '& 
If(WildMatch(
SubField(TextBetween(vColorFormatString,'||','||',1),'|',6),'*Empty*'),'',
num($(=SubField(TextBetween(vColorFormatString,'||','||',1),'|',6)),'# ##0,00')&' %') 
,
If(WildMatch(
SubField(TextBetween(vColorFormatString,'||','||',1),'|',4),'*Среднего*'),'',
num($(=SubField(TextBetween(vColorFormatString,'||','||',1),'|',5)),'# ##0.0')) & ' '& If(WildMatch(
SubField(TextBetween(vColorFormatString,'||','||',1),'|',6),'*Empty*'),SubField(%EdName,',',1),
num($(=SubField(TextBetween(vColorFormatString,'||','||',1),'|',6)),'# ##0.0')& ' ' &SubField(%EdName,',',1) )
)& ')'

Ужас какой-то, правда?:)

Теперь думаю многим стало понятно, что вся логика реализована на текстовой строке. Собрали строку – разобрали строку!

Строка в сборе имеет вид

||RWA %|RWA %|RGB(255,0,0)|Выше среднего|num(vColorFormatValue1/100,'# ##0,00')|Empty
||КОД %|КОД %|RGB(255,0,0)|Ниже среднего|num(vColorFormatValue1/100,'# ##0,00')|Empty
||НОД %|НОД %|RGB(255,0,0)|Ниже среднего|num(vColorFormatValue1/100,'# ##0,00')|Empty
||RORWA%|RORWA%|RGB(255,0,0)|Ниже среднего|round(num(124.63),0.0001)|Empty
||EL %|EL %|RGB(255,255,0)|Выше среднего|round(num(124.62),0.0001)|Empty
||EL %|EL %|RGB(0,176,80)|Ниже среднего|round(num(124.62),0.0001)|Empty
||ОД %|ОД %|RGB(0,112,192)|Ниже среднего|round(num(124.62),0.0001)|Empty
||

Разберем строку

||ЧтоКрасим|ПоКакомуЗначению|Цвет |Условие|Значение1|Значение2

Индексы в части SubField(TextBetween(vColorFormatString,'||','||',1),'|',4) — выражение каждого параметра выделяется '||' '||', свойство выражения разделены одиночным '|' таким образом индексы 1 и 4 говорят взять первый блок параметров и из него четвертое значение.

Два значения (Значение1, Значение2) для параметра «между».

Поехали дальше…

Удаление параметра по сути «реплейс» значения в строке.

Код выполнения удаления параметра условного форматирования

=Replace(vColorFormatString, '||' & TextBetween(vColorFormatString,'||','||',1),'')

Блок 3. Разработка функционального меню2

Картинка1

Картинка2

Картинка3

Основное меню сбора параметров. Опять же не буду останавливаться на элементах списков и описания их логики скрытия закрытия.

Основная логика для нашей строки, сбор всего, что накликал пользователь в этом меню зашита на кнопку «готово».

Действие «установить переменную» - vColorFormatString, код:

=If(
SubStringCount(vColorFormatString,'||')<11 
AND
(	(vColorParamByClause=0 AND (
								(Len(GetFieldSelections(%ColorFieldName))>0 AND Len(GetFieldSelections(%ColorFieldNameClause))>0) 
								AND 
								(GetFieldSelections(%ColorFieldName) <> GetFieldSelections(%ColorFieldNameClause))
								))
	OR 
	(vColorParamByClause=1 AND  Len(GetFieldSelections(%ColorFieldNameClause))>0)
),
If(vColorParamByClause=0,
if(len(vColorFormatString)<1, 
'||' & Trim(GetFieldSelections(%ColorFieldName,'|',50))  &'|' & Trim(GetFieldSelections(%ColorFieldNameClause,'|',50))  &'|' & $(=only({<%ColorDisplayName={"$(=SubField(concat(%ColorDisplayName,',',%SortColorList),',',1))"}>}%ColorRGBValue)) &'|'& GetFieldSelections(%ColorDisplayNameFormat,'|',1) & '|'& vColorFormatValue1Transform & '|'& If(GetFieldSelections(%ColorDisplayNameFormat,1) ='Между', vColorFormatValue2Transform,'Empty') & '||',
 vColorFormatString  & Trim(GetFieldSelections(%ColorFieldName,'|',50)) &'|' & Trim(GetFieldSelections(%ColorFieldNameClause,'|',50))  &'|' & $(=only({<%ColorDisplayName={"$(=SubField(concat(%ColorDisplayName,',',%SortColorList),',',1))"}>}%ColorRGBValue)) &'|'& GetFieldSelections(%ColorDisplayNameFormat,'|',1) & '|'& vColorFormatValue1Transform & '|'& If(GetFieldSelections(%ColorDisplayNameFormat,1) ='Между', vColorFormatValue2Transform,'Empty') &'||'
)
,
if(len(vColorFormatString)<1, 
'||' & Trim(GetFieldSelections(%ColorFieldNameClause,'|',50)) &'|' &  Trim(GetFieldSelections(%ColorFieldNameClause,'|',50)) &'|' & $(=only({<%ColorDisplayName={"$(=SubField(concat(%ColorDisplayName,',',%SortColorList),',',1))"}>}%ColorRGBValue)) &'|'& GetFieldSelections(%ColorDisplayNameFormat,'|',1) & '|'& vColorFormatValue1Transform & '|'& If(GetFieldSelections(%ColorDisplayNameFormat,1) ='Между', vColorFormatValue2Transform,'Empty') & '||',
 vColorFormatString & Trim(GetFieldSelections(%ColorFieldNameClause,'|',50))&'|' & Trim(GetFieldSelections(%ColorFieldNameClause,'|',50))  &'|' & $(=only({<%ColorDisplayName={"$(=SubField(concat(%ColorDisplayName,',',%SortColorList),',',1))"}>}%ColorRGBValue)) &'|'& GetFieldSelections(%ColorDisplayNameFormat,'|',1) & '|'& vColorFormatValue1Transform & '|'& If(GetFieldSelections(%ColorDisplayNameFormat,1) ='Между', vColorFormatValue2Transform,'Empty') &'||'
)
),
vColorFormatString
)

Блок 4. Разбираем текст

Код для переменных vColorFormatString1…10.

Ключевой момент для каждой из 10 переменных меняется индекс для SubField SubField(TextBetween(vColorFormatString,'||','||',1) – vColorFormatString1, индекс 1.

Для vColorFormatString4 – соответственно
SubField(TextBetween(vColorFormatString,'||','||',4)

Код переменных парсинга абсолютных значений

=If(Len(SubField(TextBetween(vColorFormatString,'||','||',4),'|',4))>0,

Pick(Match(SubField(TextBetween(vColorFormatString,'||','||',4),'|',4)
,'Больше','Меньше','Между','Равно','Первые n элементов','Первые n%','Последние n элементов','Последние n%','Выше среднего','Ниже среднего'),
//Больше
'round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)'
& '>' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Меньше
'round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)' 
& '<' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Между
'round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)'
& '>' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)') &' AND '&
'round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)' 
& '<' &  $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',6)'),
//Равно
'round(sum(DISTINCT ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)'
& '=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Первые n элементов
'num(rank(if(round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)>0,
sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']))/$(=%Value),1))' 
& '<=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Первые n%
'100-((sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']) / SUM( TOTAL ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']))*100) < ' 
& '100-' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)')
,
//Последние n элементов
'num(rank(if(round(sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])/$(=%Value),1)>0,
-sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']))/$(=%Value),1))'  
& '<=' & $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Последние n%
'((sum(['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']) / SUM( TOTAL ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &']))*100) < ' 
& $(='SubField(TextBetween(vColorFormatString,'&chr(39)&'||'&chr(39)&','&chr(39)&'||'&chr(39)&',4),'&chr(39)&'|'&chr(39)&',5)'),
//Выше среднего
'sum(DISTINCT ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])>= Median(TOTAL <$(vGroupByList)> ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])',
//Ниже среднего
'sum(DISTINCT ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])<= Median(TOTAL <$(vGroupByList)> ['& Trim(SubField(TextBetween(vColorFormatString,'||','||',4),'|',2)) &'])'
),'0>1')

Для процентных содержание посложнее

=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')

Разница в том, что формулы относительных значений я так же зашил в переменные и подставляю их в зависимости от содержания строки. По-другому не работало или работало не всегда стабильно, считалось криво… И вообще первоначально абсолютные и относительные значения были в одной переменной парсинга и это было адово громоздко, поиск ошибки кода или расчетов, разобрать написанное, было практически не возможно. Поэтому принято решение разделить — абсолютные парсим в одних переменных, процентные в других.

Блок 5. Добавляем настройки в таблицу

Ну и главное, теперь нужно красить таблицу на основе того что мы распрасили, код для всех полей таблицы идентичен, за исключение входного условия именования поля, поэтому пришлось забивать всё это дело вручную, ни какой автоматизации, точнее где-то на 15 столбце, автоматизм вырабатывается в мышцах пальцев, развивается мышечная память. Далее

например для поля “CRM ID” таблицы код имеет вид

if(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',1),'|',1)) AND ($(vColorFormatString1) OR $(vColorFormatStringPerc1)),$(vColorFormat1),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',2),'|',1)) AND ($(vColorFormatString2) OR $(vColorFormatStringPerc2)),$(vColorFormat2),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',3),'|',1)) AND ($(vColorFormatString3) OR $(vColorFormatStringPerc3)),$(vColorFormat3),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',4),'|',1)) AND ($(vColorFormatString4) OR $(vColorFormatStringPerc4)),$(vColorFormat4),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',5),'|',1)) AND ($(vColorFormatString5) OR $(vColorFormatStringPerc5)),$(vColorFormat5),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',6),'|',1)) AND ($(vColorFormatString6) OR $(vColorFormatStringPerc6)),$(vColorFormat6),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',7),'|',1)) AND ($(vColorFormatString7) OR $(vColorFormatStringPerc7)),$(vColorFormat7),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',8),'|',1)) AND ($(vColorFormatString8) OR $(vColorFormatStringPerc8)),$(vColorFormat8),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',9),'|',1)) AND ($(vColorFormatString9) OR $(vColorFormatStringPerc9)),$(vColorFormat9),
If(Match('CRM ID',SubField(TextBetween(vColorFormatString,'||','||',10),'|',1)) AND ($(vColorFormatString10) OR $(vColorFormatStringPerc10)),$(vColorFormat10),
))))))))))

например для поля “AMOUNT” таблицы код имеет вид

if(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',1),'|',1)) AND ($(vColorFormatString1) OR $(vColorFormatStringPerc1)),$(vColorFormat1),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',2),'|',1)) AND ($(vColorFormatString2) OR $(vColorFormatStringPerc2)),$(vColorFormat2),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',3),'|',1)) AND ($(vColorFormatString3) OR $(vColorFormatStringPerc3)),$(vColorFormat3),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',4),'|',1)) AND ($(vColorFormatString4) OR $(vColorFormatStringPerc4)),$(vColorFormat4),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',5),'|',1)) AND ($(vColorFormatString5) OR $(vColorFormatStringPerc5)),$(vColorFormat5),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',6),'|',1)) AND ($(vColorFormatString6) OR $(vColorFormatStringPerc6)),$(vColorFormat6),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',7),'|',1)) AND ($(vColorFormatString7) OR $(vColorFormatStringPerc7)),$(vColorFormat7),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',8),'|',1)) AND ($(vColorFormatString8) OR $(vColorFormatStringPerc8)),$(vColorFormat8),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',9),'|',1)) AND ($(vColorFormatString9) OR $(vColorFormatStringPerc9)),$(vColorFormat9),
If(Match('AMOUNT',SubField(TextBetween(vColorFormatString,'||','||',10),'|',1)) AND ($(vColorFormatString10) OR $(vColorFormatStringPerc10)),$(vColorFormat10),
))))))))))

На этом в общем-то всё. Если описывать каждый элемент меню, получится полноценная книженция, вроде методички. Поэтому извините, если кому-то не хватило деталей.

P.S.

Я понимаю, что скорее всего это можно было сделать куда изящнее, но когда ты уже в процессе, тогда только щепки летят. Я не очень хотел описывать все подробности реализации меню, да и не зачем. По большому счету статья предназначена для того, чтобы показать, как можно сделать, возможно кого-то вдохновит, появится более крутая идея реализации.

Tags:

Hubs: