filter pane with year

[Ethel]

Hello, could you please help me with this problem.

I have a two concatenated tables with  field "year".  I have filter pane. In the filter pane I have formula for year

=aggr(only({<Year = {">=$(=Max(Total Year)-4)"}>}Year),Year) 

(I'm showing the last 5 years)

But In the first table my years start from 2022, so I have to show 2022-2018.

In the second table years start from 2020, so I should show 2020-2016

When I chose parameter from the first table, I see 2022-2018

When I chose parameter from the second table, I see 2020-2016. 

BUT when I choose parameters from both tables, I see 2022-2018 instead of 2022-2016. 

How can I fix it?

Thanks a lot in advance!!

[mato32188 · Accepted solution]

Hi @Ethel ,

I would suggest you to go to the script editor and create new field for each table.

But first, we need to find max year for each table.

Load script editor:

tmp_MaxYear1stTable:

load

max(Year) as MaxYear1stTable

From first table;

tmp_MaxYear2ndTable:

load

max(Year) as MaxYear2ndTable

From second table;

let vMaxYear1stTable = peek('MaxYear1stTable',0,'tmp_MaxYear1stTable'); // to be used for comparison in 1st table

let vMaxYear2ndTable = peek('MaxYear2ndTable',0,'tmp_MaxYear2ndTable'); // to be used for comparison in 2nd table

drop tables tmp_MaxYear1stTable, tmp_MaxYear2ndTable;

table1:

Load

xx,

yy,

zz,

if(Year<$(vMaxYear1stTable )-4, null(), Year) as TotalYear

From first table;

concatenate(table1)

table2:

Load

xx,

yy,

zz,

if(Year<$(vMaxYear2ndTable )-4, null(), Year) as TotalYear

From second table;

And then use TotalYear as a dimension in filter pane. It should work.

BR

m

[Ethel]

Thank you very much!

I was trying to create  max(Year) as MaxYear1stTable, but for some reason was getting an error message

"Invalid expression". Do you know why it could happen?

[Ethel]

Thank you! It worked!

[Ethel]

I have 10 tables that I'm working with. This expression works for all table except one

if(Year<$(vMaxYear1stTable )-4, null(), Year) as TotalYear

It shows 10 last years(all years) instead of 5. 

I tried to ( Num($(vMaxYear1stTable)-4 ))

and

let vMaxYear1stTable= Num(peek('MaxYear1stTable,0,'tmp_MaxYear1stTable'));

 it helped for years in other tables, but it's not working for this table. 

Is there anything else I can do? 

Thanks a lot in advance!

[Ethel]

It works! I have updated everything and it works!

Thanks a lot!!!