topic Re: Help with setting bar chart color based on year in QlikView

Help with setting bar chart color based on year

Fri, 12 Feb 2016 16:38:07 GMT

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!

Re: Help with setting bar chart color based on year

Fri, 12 Feb 2016 18:45:07 GMT

Try like below:

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

Re: Help with setting bar chart color based on year

sunny_talwar — Fri, 12 Feb 2016 19:13:25 GMT

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

Re: Help with setting bar chart color based on year

mambi — Fri, 12 Feb 2016 19:15:34 GMT

this one :

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

Re: Help with setting bar chart color based on year

rwunderlich — Fri, 12 Feb 2016 19:21:00 GMT

What this value of

=$(vMaxSelectedYr)

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

-Rob

Re: Help with setting bar chart color based on year

Fri, 12 Feb 2016 19:46:04 GMT

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?

Re: Help with setting bar chart color based on year

sunny_talwar — Fri, 12 Feb 2016 21:20:53 GMT

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.

Re: Help with setting bar chart color based on year

Tue, 16 Feb 2016 19:51:37 GMT

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

Re: Help with setting bar chart color based on year

sunny_talwar — Tue, 16 Feb 2016 21:03:45 GMT

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

Re: Help with setting bar chart color based on year

Tue, 16 Feb 2016 21:07:48 GMT

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

Re: Help with setting bar chart color based on year

Tue, 16 Feb 2016 21:10:45 GMT

This works, but only for current year of 2016. 2015 and 2014 are still 1 solid color.

=Pick(Match(Only({<Year>} Year), $(vMaxSelectedYr), ($(vMaxSelectedYr)-1), ($(vMaxSelectedYr)-2)),
(if(Market = 'Secondary', RGB(201,187,23), blue())),
(if(Market = 'Secondary', RGB(45,64,199), cyan())),
(if(Market = 'Secondary', RGB(34,235,2),If(Market = 'New Issue',RGB(100,200,210),Green()))))

Re: Help with setting bar chart color based on year

sunny_talwar — Tue, 16 Feb 2016 21:27:52 GMT

Try this:

=Pick(Match(Only({<Year>} Year), $(vMaxSelectedYr), ($(vMaxSelectedYr)-1), ($(vMaxSelectedYr)-2)),

(If(Only({<Year>} Market) = 'Secondary', RGB(201,187,23), blue())),

(If(Only({<Year>} Market) = 'Secondary', RGB(45,64,199), cyan())),

(If(Only({<Year>} Market) = 'Secondary', RGB(34,235,2), If(Only({<Year>} Market) = 'New Issue',RGB(100,200,210),Green()))))

Re: Help with setting bar chart color based on year

Tue, 16 Feb 2016 21:33:17 GMT

Works like a charm, thanks Sunny!

Re: Help with setting bar chart color based on year

sunny_talwar — Tue, 16 Feb 2016 21:49:31 GMT

Awesome