Limit Data Loading based on most recent date

[clstuart]

I am attempting to limit the data loaded by a particular table. My limitation is that I only wish to pull the data starting 3 years prior to the most recent date from the table. 

I am currently trying to use the Where statement upon loading the table, it looks like this

Where year(myDate) >= year(Max(myDate))-3;

This returns "aggregation functions are not allowed here".

Before I used a "let" statement to load the data from 3 years prior to today(), but I'm not sure how to scope it. 

Where can I place a "let startYear = year(Max(myDate))-3;" so the myDate will be in scope and startYear will also be in scope for the Where?

[maxgro · Accepted solution]

If the table with the dates is A
A:
load
DATE(MAKEDATE(2020) + FLOOR(rand()*4000)) as Date
AutoGenerate 10000;

Set  a variable with the max year from A

M:
LOAD YEAR(MAX(Date))-3 as MaxYear Resident A;

LET vStartYear = PEEK('MaxYear');

Filter the table A with the variable

AA:
NOCONCATENATE LOAD * RESIDENT A WHERE Year(Date) >= $(vStartYear);

DROP TABLE A;

[clstuart]

Thank you, this worked. 👍

[clstuart]

Hello Maxgro. 

One question about this solution. After implementation I noticed that the key fields for rows with dates outside of the date range are still populated. 

So for example a row that originally had a date outside the cutoff range no longer has any information in it except for the ID numbers. How do I get rid of those?