Loading data only for the latest 24 months

[ingoniclas]

Hi,

I want to load sales data only for the latest 24 months. I tried to realize it with the script below, but QV always comes up with an error message:

Load

Product,

SalesDate,

Sales

from table1 where ([SalesDate]>Date(addmonths(max(SalesDate), -23), 'DD.MM.YYYY'));

Any suggestions? I'd appreciate it.

Thanks, Ingo

[maxgro · Accepted solution]

try with a variable, bold in the script below

// test data

T:

Load

     iterno() as  Product,

     Date( makedate(2014) + floor(rand()*365*4)) as SalesDate,

     rand()*100 as Sales

AutoGenerate 1

While IterNo() <= 10000;

// calc the date from and set the variable

M:

load max(SalesDate) as MaxSalesDate Resident T;

LET vMaxSalesDate = Date( AddMonths(Peek('MaxSalesDate', 0, T), -24));

DROP Table M;

// filter with the variable

F:

NoConcatenate LOAD *

Resident T

where SalesDate > '$(vMaxSalesDate)';

DROP Table T;

[Anil_Babu_Samineni]

Alternatively, If you have full dates you can work like

Load

Product,

SalesDate,

Sales

from table1 where SalesDate>Date(addyears(Today(), -2), 'DD.MM.YYYY') and SalesDate<=Date(Today(), 'DD.MM.YYYY');

[ingoniclas]

Hi Massimo,

it works! Great! Many thanks!

Ingo

[ingoniclas]

Hi Anil,

unfortunately, using today() is not an option. But thanks for the help.

Cheers,

Ingo