topic Re: Rolling 3 months in Script in QlikView

Rolling 3 months in Script

kevbrown — Wed, 03 Aug 2016 13:47:45 GMT

I need my script to only pull in data from the current month and the previous 2 months, how do i do that?

sunny_talwar — Wed, 03 Aug 2016 13:50:53 GMT

May be like this:

TempTable:

LOAD Date(MonthStart(Today(), -2)) as Date

AutoGenerate Today() - MonthStart(Today(), -2);

FactTable:

LOAD AllFields,

DateField

FROM....

Where Exists(Date, DateField);

kevbrown — Wed, 03 Aug 2016 13:56:44 GMT

Thank sunny but that just gave me 01/06/2016

juraj_misina — Wed, 03 Aug 2016 13:59:37 GMT

You can try combination of sorting, peek and rangesum.

LOAD

Month,

Value,

RangeSum(Value, Peek(Value, -1), Peek(Value, -2)) as 3M_Cumulative

From Source

Order By Month;

Anonymous — Wed, 03 Aug 2016 14:02:40 GMT

maybe use a where clause like:

where datefield >=addmonths(monthstart(today()),-2)

kevbrown — Wed, 03 Aug 2016 14:03:48 GMT

Just needed to be this - Where Date>=(MonthStart(Today(), -2)

Thanks

sunny_talwar — Wed, 03 Aug 2016 14:05:27 GMT

Good to use Where Exists if you are looking to do an optimized QVD load

kevbrown — Wed, 03 Aug 2016 14:13:33 GMT

Ok, it's a fairly small load so should be ok but if it begins to slow I'll look to add it

sunny_talwar — Wed, 03 Aug 2016 14:15:14 GMT

Sounds good