Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lbunnell
Creator
Creator

Assign load script applymap values to variable for chart colors

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?

Colors in charts

Instead of this

  1. Load the color definitions into a mapping table:
       ProductColors:
       Mapping Load [Account Type], Rgb(Red,Green,Blue) as AccountTypeColor From ChartColors
  2. Use this mapping table when loading the products table, creating a new field for the product color:
       Applymap('ChartColors', [Account Type] , lightgray()) as AccountTypeColor

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 TypeColor
SavingsvSTDarkBlue
CheckingvSTDarkOrange
InvestmentvSTMediumBlue
Money MarketvSTLightBlue
......
1 Solution

Accepted Solutions
lbunnell
Creator
Creator
Author

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)

)

View solution in original post

7 Replies
lbunnell
Creator
Creator
Author

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?

sunny_talwar

Would you be able to share a sample to see what exactly are you looking to get?

marcus_sommer

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

lbunnell
Creator
Creator
Author

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.

marcus_sommer

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

lbunnell
Creator
Creator
Author

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)

)