topic Re: How do I pull data from a rolling 18 months in the Script? in QlikView

How do I pull data from a rolling 18 months in the Script?

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

I would like to pull in 18 full months of data on a rolling basis. I do not want any partial months except for the current month. How would I accomplish this?

I experimented with the following let statements with no luck

LET vStartDate = (TODAY-547);
LET v18MonthsAgo = DATE(Month(vStartDate)-01-year(vStartDate));

***

Load DATE("DATE_CHANGED", 'MM/DD/YYYY') as DateChanged;

FROM MyTable

WHERE "DATE_CHANGED" > '$(v18MonthsAgo)'

Re: How do I pull data from a rolling 18 months in the Script?

sunny_talwar — Wed, 08 Nov 2017 19:53:33 GMT

May be this

LET vStartDate = Num(MonthStart(Today(), -18));

Load DATE("DATE_CHANGED", 'MM/DD/YYYY') as DateChanged;

FROM MyTable

WHERE "DATE_CHANGED" > '$(vStartDate)'

Re: How do I pull data from a rolling 18 months in the Script?

Anonymous — Wed, 08 Nov 2017 20:03:10 GMT

I get error message "Conversion failed when converting date and/or time from character string"

Re: How do I pull data from a rolling 18 months in the Script?

sunny_talwar — Wed, 08 Nov 2017 20:04:04 GMT

Can you share a screenshot of the error message

Re: How do I pull data from a rolling 18 months in the Script?

Anonymous — Wed, 08 Nov 2017 20:10:31 GMT

Re: How do I pull data from a rolling 18 months in the Script?

sunny_talwar — Wed, 08 Nov 2017 20:11:53 GMT

Try this

LET vStartDate = Date(MonthStart(Today(), -18), 'DDMMMYYYY');

Load DATE("DATE_CHANGED", 'MM/DD/YYYY') as DateChanged;

FROM MyTable

WHERE "DATE_CHANGED" > '$(vStartDate)'

Re: How do I pull data from a rolling 18 months in the Script?

Anonymous — Wed, 08 Nov 2017 20:27:36 GMT

As far as I can tell, this worked! Thank you for your help and quick responses!