Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to use the instructions provided here with a slight variation in that I'd like to use a variable for the chart background color based on the value like this:
Applymap('ChartColors', [Account Type] , lightgray()) as AccountTypeColor
However when I set the Background color in the chart expression to
$(AccountTypeColor) the colors of the chart do not change. Is there a way to accomplish this using a field value from table as a variable in the design layer?
Instead of this
I've created custom colors include file like the following:
SET vLightOrange = RGB(252,213,180);
SET vLighterOrange = RGB(253,233,217);
SET vDarkerOrange = RGB(247,150,70);
SET vPurple = RGB(105,84,150);
...
along with the following in the load script:
ChartColors:
Mapping LOAD [Account Type],
Color
FROM
(ooxml, embedded labels, table is Sheet1);
which looks like this
Account Type | Color |
Savings | vSTDarkBlue |
Checking | vSTDarkOrange |
Investment | vSTMediumBlue |
Money Market | vSTLightBlue |
... | ... |
I found a solution from Stefan Wuhl:
Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table
=pick( match( VariableName, 'VK1_ntf','VK1_ntf_int','VK2_ntf','VK2_ntf_int'),
$(VK1_ntf),
$(VK1_ntf_int),
$(VK2_ntf),
$(VK2_ntf_int)
)
See if this helps
Maintaining Color of the Dimensions in QlikView – Learn QlikView
That's close, but I was looking to set the colors to a variable and have the Account/Color table contain the variable $(vDarkBlue) name with the CustomColors include file holding the RGB values. Maybe that's not possible?
Would you be able to share a sample to see what exactly are you looking to get?
It's not complete clear for me how do you tried to apply the colors with the mapping and how the variables are later called. But I assume that you don't assigned the variable-value per mapping to the dimension (if it would be logically quite the same like the suggestion from HIC in the above mentioned link) else the variable-name as string. This of course will lead to the problem that it is just a string until you used a $-sign expansion - but this results in a new adhoc-variable and is not working on a dimension-level.
Beside this I don't see any added value to implement a color-logic on a variable-basis compared to a table- and field-value approach. If you ever get it to work it will be more expensive and complex as the suggestion from HIC - why not just copying / adapting this approach?
- Marcus
Yes, I could use the other approach, but I already had the custom color include file with numerous RGB values created and I want to know, for other future use cases, whether it was possible to convert a field value like this one in Henric's example from an ApplyMap created value to a create an ad hoc variable which I am attempting to use as the Chart Expression background color per the example.
I've tried setting a variable in Variable Overview like vAccountTypeColor = FieldValue('AccountTypeColor') then applying $(vAccountTypeColor) to the Background of the Chart Expression.
Also tried simply setting the Chart Expression directly to =$('AccountTypeColor'), however neither of these approaches seem to work.
Basically, I'd like to know if there is a way to use the values from a field in the load script to create a dynamic variable.
I don't want to say that's not possible to implement a variable-solution but I doubt that it would make much sense to go this (probably terrible) way.
What speaks against the approach to use the variable-file to create a table?
- Marcus
I found a solution from Stefan Wuhl:
Tricky Variable - Use Field Value as Variable Name and return the Variable Value in Table
=pick( match( VariableName, 'VK1_ntf','VK1_ntf_int','VK2_ntf','VK2_ntf_int'),
$(VK1_ntf),
$(VK1_ntf_int),
$(VK2_ntf),
$(VK2_ntf_int)
)