topic Re: Prior Month in App Development

Prior Month

Wed, 14 Sep 2016 11:57:41 GMT

Hello All,

I have several SQL queries populating QVDs within Qlik Sense. I am attempting to automate my apps so that at the beginning of every month a task reloads the data and brings in the most recent data. Each SQL table has an 'AsOfDate' field that indicates the relative month of the data (eg 2016-08-01 is the most recent data in each SQL table.)

What I am attempting to do is use a where clause in the Data Load Editor to target last month's data. Currently, I have to go in each month and update the where clause to the most recent month. (where AsOfDate = '2016-08-01'; I want to say there is a way to do it with today()-1, but I cant seem to make it work.

Any help would be greatly appreciated.

Thanks,

Todd

Re: Prior Month

Anonymous — Wed, 14 Sep 2016 17:00:57 GMT

Todd - Try and create a variable to generate a date like:

LET vMinDate = Date(MonthStart(Today()-1),'YYYY-MM-DD');

And then use it in your where clause:

LOAD * From MyQVD.qvd(qvd)

Where LoadDate >= '$(vMinDate)';

Re: Prior Month

Wed, 14 Sep 2016 17:24:54 GMT

Hi Chris,

Thanks for the timely response. This absolutely works for creating the date, however it only seems to respond with the '>' symbol. As I am trying to report on just the one specific month, I am trying to limit it to just that month. However, by using the '=' alone, no records are being pulled. Any ideas?

Thanks

Re: Prior Month

Anonymous — Wed, 14 Sep 2016 19:01:56 GMT

Oh - I see what you're saying. Your best bet is to probably turn that date to a number and do your date math that way. If that doesn't work, you could use the Match fuction to say Where Match(YourDate,'$(vMinDate)');

Re: Prior Month

sunny_talwar — Thu, 15 Sep 2016 03:41:37 GMT

May be try this:

LET vDate = Num(MonthStart(Today()-1));

LOAD *

From MyQVD.qvd (qvd)

Where MonthStart(LoadDate) = $(vDate);