Script: Limit Data to YTD Previous Year & YTD Current Year

[Kathi]

Hi everyone, 

I need some help with scripting, please. 

I have sales data from 01.01.2019 to 14.05.2020. 
What I would like to do is limit the data upload to YTD of the previous year and YTD of the current year: 
Example:
01.01.2019 - 14.05.2019 and
01.01.2020 - 14.05.2020
The app gets updated daily, so the end date should be dynamic and always show yesterday's date.

Current script for upload looks like this: 
[Report 1]:
LOAD
[Date] AS Date,
[Market],
[Sales],
FROM xxxxxfolderxxxxx

How can I do this? I am lost. 

Thanks so much in advance. 
K

[jwjackso]

You can use variables, you will probably need to format the date so that it matches your data:

Let vMinDate=YearStart(AddMonths(Today(),-12));
Let vMaxDate=Today();

[Report 1]:
LOAD
[Date] AS Date,
[Market],
[Sales],
FROM xxxxxfolderxxxxx

where [Date] >= $(vMinDate) and [Date] < $(vMaxDate);

[Kathi · Accepted solution]

Thanks for the help. 

I changed it a bit so that the previous Year YTD and the current Year YTD are selected and it works just fine 🙂 

This was my final script: 

Let vMinDatePY=YearStart(Addyears(Today(),-1));
Let vMaxDatePY=AddYears(Today(),-1);
Let vMinDateFY=YearStart(Today());
Let vMaxDateFY=Today();

[Report 1]:
LOAD
[Date] AS Date,
[Market],
[Sales],
FROM xxxxxfolderxxxxx

where Date >= '$(vMinDatePY)' and Date < '$(vMaxDatePY)' or Date >= '$(vMinDateFY)' and Date < '$(vMaxDateFY)'