Get latest 5 year data only

[Qlik1_User1]

HI All,

I am using this expression

=Aggr(if(Rank(Count(reporting_year))<6,reporting_year),reporting_year) this gives me latest 5 year data.

But as soon as i select reporting year from filter, in bar chart only selected year is coming.
Is there a way if user select any reporting year from filter , still latest 5 year data should be visible not the select reporting year.

[vinieme12 · Accepted solution]

Try below, for calculated dimension, but in this case you also need to modiy your measure to ignore selections in field reporting_year

Dimension

=Aggr(only({1<reporting_year={">$(=Max(reporting_year)-5)"}>}  reporting_year ),reporting_year) 

Measure

sum({<reporting_year= >} Sales)

Ideally you can restrict your measure instead of the dimension. for example

Dimension = reporting_year

Measure =

sum({<reporting_year={">$(=Max(reporting_year)-5)"} >} Sales)