Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Not applicable

## Help with setting bar chart color based on year

We have a bar chart that we always want to display 3 years no matter what. If the user picks 2016, we want to show 2016, 2015, and 2014. We also want to have the colors defined. Everything works when either no year is selected, or if 3 years are selected. So if the user picks no year, we see 2016, 2015, and 2014 and the colors we want. If they pick 2016,2015, and 2014 we see all 3 years and the colors we want.

The problem is when the user only picks one year, say 2016. We still see all 3 years, but the colors stop working.

Here is the formula for the always 3 years on the chart:

=Sum({<Year = {\$(=Max(Year)), \$(=Max(Year) - 1), \$(=Max(Year) - 2)}>} AdmBrokrCommBaseAmt)/1000

Here is the formula for the colors. We are using this in the background color expression:

Pick(Match(Year,\$(vMaxSelectedYr),(\$(vMaxSelectedYr)-1),(\$(vMaxSelectedYr)-2)),RGB(201,187,23),RGB(45,64,199),RGB(34,235,2))

If someone could either provide us with a solution, or help us understand why it doesn't work when only 1 year is selected, that would be great. thanks!

1 Solution

Accepted Solutions
MVP

Can you try this for color expression:

Pick(Match(Only({<Year>} Year), \$(vMaxSelectedYr), (\$(vMaxSelectedYr)-1), (\$(vMaxSelectedYr)-2)), RGB(201,187,23), RGB(45,64,199), RGB(34,235,2))

13 Replies
Not applicable
Author

Try like below:

=Sum({<Year = {"\$(=Max(Year))", "\$(=Max(Year) - 1)", "\$(=Max(Year) - 2)"}>} AdmBrokrCommBaseAmt)/1000

MVP

Can you try this for color expression:

Pick(Match(Only({<Year>} Year), \$(vMaxSelectedYr), (\$(vMaxSelectedYr)-1), (\$(vMaxSelectedYr)-2)), RGB(201,187,23), RGB(45,64,199), RGB(34,235,2))

Creator III

this one :

Pick(Match(Only({1}Year),\$(vMaxSelectedYr),(\$(vMaxSelectedYr)-1),(\$(vMaxSelectedYr)-2)),RGB(201,187,23),RGB(45,64,199),RGB(34,235,2))

What this value of

=\$(vMaxSelectedYr)

in a text object when you select only 2016? What is the expression in vMaxSelectedYr?

-Rob

Not applicable
Author

Thanks Sunny, it's working as we want it to now.

If it's brief, can you explain what was happening with my expression and why yours works?

MVP

When you selected a Year, the year field within the match function was getting filtered to that specific year and you were not able to use it for the previous two years. When we use Only({<Year>} Year) we stopped it from filtering based on a selection in Year field and it worked.

Not applicable
Author

How can I add another field to this expression? We have this as a bar chart with the Amount being split between New and Old. So for 2016 old might be red, new would be blue. The field name is Market

Pick(Match(Only({<Year>} Year), \$(vMaxSelectedYr), (\$(vMaxSelectedYr)-1), (\$(vMaxSelectedYr)-2)), RGB(201,187,23), RGB(45,64,199), RGB(34,235,2))

MVP

If I am understanding this correctly, what happens if you print Market as your second dimension?

Not applicable
Author

Basically I need to combine these two statements

Pick(Match(Only({<Market>} Market), 'Secondary', 'New Issue'), RGB(201,187,23), RGB(45,64,199))

Pick(Match(Only({<Year>} Year), \$(vMaxSelectedYr), (\$(vMaxSelectedYr)-1), (\$(vMaxSelectedYr)-2)), RGB(201,187,23), RGB(45,64,199), RGB(34,235,2))

Community Browser