Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
Try like below:
=Sum({<Year = {"$(=Max(Year))", "$(=Max(Year) - 1)", "$(=Max(Year) - 2)"}>} AdmBrokrCommBaseAmt)/1000
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))
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
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?
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.
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))
If I am understanding this correctly, what happens if you print Market as your second dimension?
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))