How can I use variable with yearstart and max date to filter two years from most recent date in data

[46897435]

I've tried the following variable:

Let StartDate = yearstart(max(Date),-2)

and then called it like this:

LOAD

*

Where Date >= '$(StartDate)'

;

However, this is not working. NOTE: Date is a date field in mm/dd/yyyy format and it is part of database imported from Access

[dplr-rn]

the max doesnt work in that way

you will need something like below

Temp:
Load
    max(OrderDate) as maxDate
Resident yourtable;


Let varMaxDate = yearstart(Peek('maxDate', 0, 'Temp'),-2);

[SukumarBera]

Solution :

This is a two steps process-

• Determine the maximum date using FieldValue and FieldValueCount functions and store that into a variable.
• Create another variable to determine the Last to Last year using YearStart function.

Please have a look at my script below . I used an INLINE LOAD with one date field.

Data:
LOAD date#(Date,'MM/DD/YYYY') AS Date Inline
[
Date
09/26/2019
09/25/2019
09/24/2019

];

Tab:
LOAD recno() AS ID,Date(max(FieldValue('Date',RecNo())),'DD-MM-YYYY') AS MaxDate
AutoGenerate FieldValueCount('Date');

LET vMaxDate=PEEK('MaxDate',0,'Tab');
Drop Table Tab;
LET vStartDate=YearStart(vMaxDate,-2);

Finally called it like this:

LOAD

*

Where Date >= '$(StartDate)'

;

Thanks & Regards,

Sukumar Bera