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

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

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

30.03.2019 12:58

mBlaze 7 2900 Источник

Задача – сделать условное форматирование таблицы «как в эксель». 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
)

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

Код для переменных 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')

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

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

Ну и главное, теперь нужно красить таблицу на основе того что мы распрасили, код для всех полей таблицы идентичен, за исключение входного условия именования поля, поэтому пришлось забивать всё это дело вручную, ни какой автоматизации, точнее где-то на 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.

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

Комментарии (7)

Asgat_Akhmetshin
01.04.2019 13:44
#19968822
может я чего-то не знаю… выглядит как статья-пиар других BI решений… у других BI-систем как-то по проще…
1. mBlaze Автор
  01.04.2019 19:06
  #19970730
  Вероятно не знаете, иначе бы написали, что данная статья представляет собой странную, излишнюю работу — ведь в решении BI платформы QlikView текущий инструментарий реализован. Но это не так, поэтому — буду рад если Вы приведете пример таких BI решений и кратко приведете возможности функционала касательно описанной тематики для конечного бизнес-пользователя и вот тогда эта статья превратится в провокационную, чтобы «пропиарить» то, что вы сейчас будете перечислять. И я лично буду рад узнать о таких. Могу сказать, что ни QlikView, ни текущий лидер pBI — с которыми я работаю, не предоставляют такой возможности в выборе параметров условного форматирования, а вот эксель точно предоставляет.:)

Asgat_Akhmetshin
02.04.2019 11:38
#19973014
Ещё бы понять о чем речь)
От excel разница не знаю в чем(криво стала ссылка). То что применимо на практике схоже.

Другой вариант, что предполагаю ты имел введу, что если форматировать столбец выбирая параметр какой-то, например от Выполнения плана ЧП, или Выполнение плана по Выручке, и пр., то через SELECTVALUE создал бы меру из этих параметров, и фильтр этих параметров. Закидываю меру в условное форматирование и настраиваю правила/условия. Выбирая параметр, например, Чистая прибыль, то у меня условное форматирование по значению чистой прибыли. Как вариант.
1. mBlaze Автор
  02.04.2019 11:49
  #19973082
  Решение, которое описано в статье даёт возможность пользователю, конечному потребителю отчета, задавать параметры условного форматирования самостоятельно, в тонком клиенте. Вы представили вариант реализации на этапе разработки, то есть при разработке отчета я задаю цветовую схему раскраски — это не проблема, но пользователь эти параметры поменять не может.
  1. Asgat_Akhmetshin
    02.04.2019 12:03
    #19973202
    не совсем понятна мысль.
    
    Без проблем можно сделать и так, чтоб пользователь сам задавал параметры форматирования(или даже вводил в поле на дашборде), или мог изменить их сам очень просто(все как в excel).
    я пример форматирования своих отчётов, и не вижу различия от условного форматирования excel.
    
    mBlaze Автор
    02.04.2019 12:45
    #19973558
    Мысль — реализовать то, что запросил заказчик. На том инструментарии который использует заказчик. Задача не предусматривала опции «изменить BI инструмент». Что касательно «даже вводил в поле на дашборде» в вашем комментарии, я с удовольствием прочитаю и добавлю в закладки для использования реализацию которую вы опишите более детально. Спасибо.:)
    
    Asgat_Akhmetshin
    02.04.2019 13:13
    #19973728
    угу, заказчик субстанция не постоянная.
    «хочу чтоб красные KPI мигали», через месяц — «убери их, бесят».

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

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

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

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

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

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

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

Комментарии (7)

Asgat_Akhmetshin

mBlaze Автор

Asgat_Akhmetshin

mBlaze Автор

Asgat_Akhmetshin

mBlaze Автор

Asgat_Akhmetshin