How do I limit the values displayed for a Dimension in a chart?

[Anonymous]

I've been trying to limit the number of Years displayed in my chart to the most recent three years.  However the 'Dimension Limits' tab in the Chart properties only lets me limit the Dimension by the 'First', 'Largest' and 'Smallest number.  Is there another way?

This is the chart without limits set -

This is how I want it to look -

Thanks

Greg

[alexandros17 · Accepted solution]

If your dimension name is Year then write as dimension (Click Edit button ...)

If(Year >= Year(Today())-3, Year, Null())

and check the box "Suppress null values

Let me know

[#3]

Hi Greg,

Alessandro gave you an answer, if "the most recent three years" for you means always last 3 callendar years.

If that is not what you mean, try to write which years you want to choose.

Regards

[Anonymous]

Thanks Alessandro.  That worked.

But it stops people from choosing other years in the list box.  Is there a way to get the chart to list the most recent three years by default but if someone chooses to show more than the most recent three years it will let them.

Thanks

Greg

[#5]

You can do it by calculating your dimension like this:

=If(GetSelectedCount(Year)=0,
If(Year >= Year(Today())-3, Year, Null()),
Year)

If you select any year, you will se this selected year/years.

If you don't select any year, you will see last 3 years (and current).

[Anonymous]

Excellent!  Thanks very much Bartłomiej