March 30, 2019 at 15:58QlikView. Conditional formatting "Like in Excel"
The task is to make conditional formatting of the table "like in Excel." QlikView is quite able to cope with the standard tasks of coloring by condition, but with the choice of parameters dynamically, and even as in Excel, the question arose: “is it like that at all ..?”.
Bottom line: there were several implementation options, several versions, considerable time was spent on testing and debugging. The refactoring of the finished implementation was carried out. I present the final version, satisfying the needs of the customer, tested and verified.
There is a table in which dimensions and expressions are selected dynamically, the formulas for calculating the values themselves are simple: sums, average, relative percentages, absolute values, and the like.
It is required to enable the user to colorize the table depending on the selected parameters. Key introduction - one column should be able to be painted in different colors, that is, multi-coloring, according to different parameters. The second key one should be the ability to colorize the field depending on another “interconnected parameters”, for example, we colorize the “purchase amount” depending on the value of “average price of products on the market”.
The following is the main part of the implementation. I will not dwell on such things as hide / close menus, creating lists, and the like. If you have questions about the details I will tell additionally.
The final result, the path to which was thorny:
I also needed to create several dozens of variables:
vColorFormatString - the assembled string
vColorFormatString1 ... 10 - designed to parse the string from vColorFormatString into 10 parameters for absolute values , this is the restriction I made, my opinion is that it is no longer needed, in general this restriction is pure ideological and not spending much time the number of parameters can be increased.
vColorFormatStringPerc1 ... 10 - designed to parse a string from vColorFormatString into 10 parameters for relative values (percent)
vColorFormat1 ... 10 - colors
Appointment:
Let us dwell on the two elements below.
The first is a display of the currently created parameters.
This area is text objects that display the current created parameters based on parsing the string. And now the first brick of this whole action:
Some kind of horror, right? :)
Now I think many have realized that all the logic is implemented on a text line. Collected a line - sorted a line!
The string in the collection has the form
|| RWA% | RWA% | RGB (255,0,0) | Above average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| CODE% | CODE% | RGB (255,0,0) | Lower than average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| GCD% | GCD% | RGB (255,0,0) | Lower than average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| RORWA% | RORWA% | RGB (255,0,0) | Below average | round (num (124.63), 0.0001) | Empty
|| EL % | EL% | RGB (255,255,0) | Above average | round (num (124.62), 0.0001) | Empty
|| EL% | EL% | RGB (0,176,80) | Below average | round (num (124.62) , 0.0001) | Empty
|| OD% | OD% | RGB (0,112,192) | Below average | round (num (124.62), 0.0001) | Empty
||
Let's parse the line
|| WhatColor | WHAT VALUE | Color | Condition | Value1 | Value2
Indexes in part SubField (TextBetween (vColorFormatString, '|| ', '||', 1), '|', 4) - an expression of each parameter stands '||' '||', property expressions separated by single '|' thus, indices 1 and 4 say to take the first block of parameters and the fourth value from it.
Two values (Value1, Value2) for the “between” parameter.
Let's go further ...
Removing a parameter is essentially a “replay” of the value in the line.
The main menu for collecting parameters. Again I will not dwell on the elements of lists and descriptions of their logic for hiding closure.
The main logic for our line is the collection of everything that the user prompted in this menu is sewn to the "ready" button.
The code for the variables is vColorFormatString1 ... 10.
The key point for each of the 10 variables is changing the index for SubField SubField (TextBetween (vColorFormatString, '||', '||', 1 ) - vColorFormatString 1 , index 1.
For vColorFormatString 4 - respectively
SubField (TextBetween (vColorFormatString, '|| ',' || ', 4 )
The difference is that I also stitched the relative value formulas into variables and substitute them depending on the contents of the string. It didn’t work in a different way or it didn’t always work stably, it was considered crooked ... And in general, initially absolute and relative values were in one parsing variable and it was hellishly cumbersome, it was practically impossible to search for a code error or calculations, to parse what was written. Therefore, it was decided to divide - absolute parsim in some variables, percent in others.
Well and most importantly, now you need to paint the table based on what we have distributed, the code for all the fields in the table is identical, except for the input condition for naming the field, so I had to hammer the whole thing manually, no automation, more precisely somewhere on column 15, automatism It is produced in the muscles of the fingers, muscle memory develops. Further
That’s basically all. If you describe each menu item, you get a full book, like a training manual. So sorry if someone lacked the details.
Bottom line: there were several implementation options, several versions, considerable time was spent on testing and debugging. The refactoring of the finished implementation was carried out. I present the final version, satisfying the needs of the customer, tested and verified.
Functional task (statement)
There is a table in which dimensions and expressions are selected dynamically, the formulas for calculating the values themselves are simple: sums, average, relative percentages, absolute values, and the like.
It is required to enable the user to colorize the table depending on the selected parameters. Key introduction - one column should be able to be painted in different colors, that is, multi-coloring, according to different parameters. The second key one should be the ability to colorize the field depending on another “interconnected parameters”, for example, we colorize the “purchase amount” depending on the value of “average price of products on the market”.
The following is the main part of the implementation. I will not dwell on such things as hide / close menus, creating lists, and the like. If you have questions about the details I will tell additionally.
The final result, the path to which was thorny:
Block 1. Preparation of data for the function menu
The code prepares the foundation for the menu lists described in block No. 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([Измерение/Значение],'Значение');I also needed to create several dozens of variables:
vColorFormatString - the assembled string
vColorFormatString1 ... 10 - designed to parse the string from vColorFormatString into 10 parameters for absolute values , this is the restriction I made, my opinion is that it is no longer needed, in general this restriction is pure ideological and not spending much time the number of parameters can be increased.
vColorFormatStringPerc1 ... 10 - designed to parse a string from vColorFormatString into 10 parameters for relative values (percent)
vColorFormat1 ... 10 - colors
Block 2. Development of the functional menu1
Appointment:
- Displays the current list of created parameters by the user.
- Ability to remove any conditional formatting option
- Invokes a menu for selecting conditional formatting options.
Let us dwell on the two elements below.
The first is a display of the currently created parameters.
This area is text objects that display the current created parameters based on parsing the string. And now the first brick of this whole action:
Text display formula
=
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) )
)& ')' Some kind of horror, right? :)
Now I think many have realized that all the logic is implemented on a text line. Collected a line - sorted a line!
The string in the collection has the form
|| RWA% | RWA% | RGB (255,0,0) | Above average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| CODE% | CODE% | RGB (255,0,0) | Lower than average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| GCD% | GCD% | RGB (255,0,0) | Lower than average | num (vColorFormatValue1 / 100, '# ## 0,00') | Empty
|| RORWA% | RORWA% | RGB (255,0,0) | Below average | round (num (124.63), 0.0001) | Empty
|| EL % | EL% | RGB (255,255,0) | Above average | round (num (124.62), 0.0001) | Empty
|| EL% | EL% | RGB (0,176,80) | Below average | round (num (124.62) , 0.0001) | Empty
|| OD% | OD% | RGB (0,112,192) | Below average | round (num (124.62), 0.0001) | Empty
||
Let's parse the line
|| WhatColor | WHAT VALUE | Color | Condition | Value1 | Value2
Indexes in part SubField (TextBetween (vColorFormatString, '|| ', '||', 1), '|', 4) - an expression of each parameter stands '||' '||', property expressions separated by single '|' thus, indices 1 and 4 say to take the first block of parameters and the fourth value from it.
Two values (Value1, Value2) for the “between” parameter.
Let's go further ...
Removing a parameter is essentially a “replay” of the value in the line.
Code for deleting conditional formatting parameter
=Replace(vColorFormatString, '||' & TextBetween(vColorFormatString,'||','||',1),'') Block 3. Development of the functional menu2
Picture1
Picture2
Picture3
The main menu for collecting parameters. Again I will not dwell on the elements of lists and descriptions of their logic for hiding closure.
The main logic for our line is the collection of everything that the user prompted in this menu is sewn to the "ready" button.
The action "set variable" is vColorFormatString, code:
=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
)Block 5. Parsing the text
The code for the variables is vColorFormatString1 ... 10.
The key point for each of the 10 variables is changing the index for SubField SubField (TextBetween (vColorFormatString, '||', '||', 1 ) - vColorFormatString 1 , index 1.
For vColorFormatString 4 - respectively
SubField (TextBetween (vColorFormatString, '|| ',' || ', 4 )
Absolute Value Parsing Variable Code
=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') For interest, the content is more complicated
=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')
The difference is that I also stitched the relative value formulas into variables and substitute them depending on the contents of the string. It didn’t work in a different way or it didn’t always work stably, it was considered crooked ... And in general, initially absolute and relative values were in one parsing variable and it was hellishly cumbersome, it was practically impossible to search for a code error or calculations, to parse what was written. Therefore, it was decided to divide - absolute parsim in some variables, percent in others.
Block 6. Add settings to the table
Well and most importantly, now you need to paint the table based on what we have distributed, the code for all the fields in the table is identical, except for the input condition for naming the field, so I had to hammer the whole thing manually, no automation, more precisely somewhere on column 15, automatism It is produced in the muscles of the fingers, muscle memory develops. Further
for example, for the “CRM ID” field of the table, the code has the form
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),
))))))))))for example, for the “AMOUNT” field of the table, the code has the form
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),
))))))))))
That’s basically all. If you describe each menu item, you get a full book, like a training manual. So sorry if someone lacked the details.
PS
I understand that most likely this could be done much more elegantly, but when you are already in the process, then only chips will fly. I didn’t really want to describe all the details of the menu implementation, and why not. By and large, the article is intended to show how it can be done, perhaps it will inspire someone, a steeper idea of implementation will appear.