Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ingoniclas
Contributor

Loading data only for the latest 24 months

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading data only for the latest 24 months

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;

View solution in original post

4 Replies
MVP
MVP

Re: Loading data only for the latest 24 months

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;

View solution in original post

Re: Loading data only for the latest 24 months

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');

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ingoniclas
Contributor

Re: Loading data only for the latest 24 months

Hi Massimo,

it works! Great! Many thanks!

Ingo

ingoniclas
Contributor

Re: Loading data only for the latest 24 months

Hi Anil,

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


Cheers,

Ingo