topic Re: Loading data only for the latest 24 months in QlikView

Loading data only for the latest 24 months

ingoniclas — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Loading data only for the latest 24 months

maxgro — Mon, 15 Jan 2018 23:29:28 GMT

try with a variable, bold in the script below

// test data

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

load max(SalesDate) as MaxSalesDate Resident T;

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

DROP Table M;

// filter with the variable

NoConcatenate LOAD *

Resident T

where SalesDate > '$(vMaxSalesDate)';

DROP Table T;

Re: Loading data only for the latest 24 months

Anil_Babu_Samineni — Tue, 16 Jan 2018 01:57:32 GMT

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

Re: Loading data only for the latest 24 months

ingoniclas — Tue, 16 Jan 2018 08:56:54 GMT

Hi Massimo,

it works! Great! Many thanks!

Ingo

Re: Loading data only for the latest 24 months

ingoniclas — Tue, 16 Jan 2018 08:58:30 GMT

Hi Anil,

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

Cheers,

Ingo