Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In our dashboard, we have a year filter; 2016, 2015, 2014, 2012, 2011 etc.
We have a bar chart where if the user picks 2014,2015,2016 we see a bar for each year. If they pick only 2016, they only see 2016. We want to make it so if they pick ONLY pick 2016, data for 2015 and 2014 will show up. If they pick 2013, 2012 and 2011 will appear.
How would I go about making the bar chart always show 3 years of data? The highest year would be the year seletced, the other 2 years would be selected year and then the two prior years.
Thank you
May be try this as your set analysis:
{<Year = {$(=Max(Year)), $(=Max(Year) - 1), $(=Max(Year) - 2)}>}
For instance if you expression was Sum(Sales), it would now become:
=Sum({<Year = {$(=Max(Year)), $(=Max(Year) - 1), $(=Max(Year) - 2)}>} Sales)
Thank Sunny, that initially solves what I'm looking for. Currently we have the value being displayed in a variable. Within that variable is an if statement. Bascially if it's 2016, we want to annualize that number, if its 2015 or <, we don't want to annualize.
I tried your formula with a simple commission field and it worked great, but when I use the variable instead of the simple field, it doesn't work. Any idea on how I implement a variable with an if statement that checks to see if max selected year = max year on calendar is true or not with your formula?
I would create a variable like this:
vVar = '=Max(Year) & ',' & (Max(Year) - 1) & ',' & (Max(Year) - 2)' (Without Single Quotes)
and try to use it like this:
=Sum({<Year = {$(vVar)}>} Sales)