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: 
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
sunny_talwar

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))

View solution in original post

13 Replies
Not applicable
Author

Try like below:


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

sunny_talwar

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))

mambi
Creator III
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)) 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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?

sunny_talwar

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))

sunny_talwar

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))