Pick(Match()) using a variable

[Anonymous]

Hey Guys,

I would like to show several different values in my line graph. Therefor I want to assign different colors to different lines.

In my coloring expression I would like to change the colors to company colors. However this isn't working.

When coloring is on auto all 3 lines will get different colors, so it should be possible to solve with a formula.

The formula is based upon one dimension with several values.

Variable contains: =getfieldselections(Item) =vRelativeSelections

pick(match(Item, '=$(vRelativeSelections)',red(),Yellow(),black()))

Thanks is advance.

[bc-thebruuu]

Can you post an application?

Did you consider to put the color in your model with a table like

Item, color

?

[ogster1974]

Have you thought of making your dimension a master item and setting the colour in there? That way you can apply it consistantly across visualizations in your app.

Regards

Andy

[sasiparupudi1 · Accepted solution]

Use your variable as

vRelativeSelections=chr(39)&GetFieldSelections(Item,chr(39)&chr(44)&chr(39)) & chr(39)

pick(match(Item, $(vRelativeSelections),red(),Yellow(),black()))

[Anonymous]

Hi Bruno,

Then I have a limited amount of colors instead of unlimited.

see qvf

[Anonymous]

Hi Andy,

Yes, but then the dimension would have one color and I want every value to have a different one. Since the dimension has several values.

Regards,

Robbert

[bc-thebruuu]

You can use an index in the color column and color(index) in the expression

[marcus_sommer]

Beside the fact that there is a slight syntax-error by missing an closing brackets for match() and one too much for the pick() your variable needs to return the same structure as if you would write this manually like:

pick(match(Item, 'Item1', 'Item2', 'Item3'),red(),Yellow(),black())

which your getfieldselections() don't return because the list-values won't be quoted. This meant you need to use a different approach for example:

pick(match(Item, '$(=concat(distinct Item, chr(39) & chr(44) & chr(39)))'), red(),Yellow(),black())

Further I suggest to apply this kind of color-assignment within the script like: Colors in charts.

- Marcus

[Anonymous]

Hi Sasidhar,

Your solution works! only one minor detail, mistake from my side at the beginning:

pick(match(item, $(vRelativeSelections)),red(),Yellow(),black())

one more) after the variable and one less at the end!

Thanks for you help!