Help with dates.

[#1]

Hello all!

Well, I'm pretty new to Qv, so go easy on me.

I'm having some issues understanding the way that Qv handles dates... what I'm trying to do is select records where the date is equal to the current month on my system. The data is in YYYY/MM.... I was trying to do GETDATE() with MID functions to extract the 'year'+/+'mo'. But to no avail.

Any suggestions?

[#2]

Use TODAY() to get date, and SUBFIELD instead of MID.

Good luck.

[#3]

What format is the TODAY() output in?

[#4]

Can you post a small snippet of your data? I'm not quite sure what you're asking... thanks!

Also, check out the QlikView help, search for "Date and Time Functions", tons of great info there.

[#5]

When I run the report I need to see totals from the current month, as well as the previous 3 months. Like I said, my data is stored as YYYY/MM (2009/12, 2009/11, 2009/10....)

When I enter...

SQL SELECT * FROM blahblah

WHERE YearMonth='2009/12'

...I get the correct output. But I want to change the WHERE clause to be a variable that automatically changes the output from month-to-month.

[#6]

should it look like this?

WHERE YearMonth='year(left(YearMonth,4))'+'/'+'month(right(YearMonth,2))'

Also, do I have to convert to int before I can subtract for previous months?

[rwunderlich · Accepted solution]

If you want to set the SQL WHERE clause to the current month, in your specified format, you can do it like this:

LET currentMonth = date(today(), 'YYYY/MM');

SQL SELECT * FROM blahblah
WHERE YearMonth='$(currentMonth)'
;

And multiple months like this:

LET currentMonth = date(today(), 'YYYY/MM');
LET month1 = date(AddMonths(today(),-1), 'YYYY/MM');

SQL SELECT * FROM blahblah
WHERE YearMonth IN ('$(currentMonth)', '$(month1)')
;

-Rob

[johnw]

(Edit: Beaten to the punch. I should have refreshed before posting.)

OK, so in your SQL database, you stored the YearMonth as a TEXT field like '2009/12'. You want to load the current month and the most recent three months. So since today is in month 2009/12, you want your select to include:

WHERE YearMonth IN ('2009/12','2009/11','2009/10','2009/09')

And then on January 1, you want it to automatically change to:

WHERE YearMonth IN ('2010/01','2009/12','2009/11','2009/10')

If so, then yes, you could handle it in the DBMS with getdate() and the like. But yes, you can also handle it by building a variable in your script.

LET MonthList = '(' & CHR(39) & date( today() ,'YYYY/MM') & CHR(39)
& ',' & CHR(39) & date(addmonths(today(),-1),'YYYY/MM') & CHR(39)
& ',' & CHR(39) & date(addmonths(today(),-2),'YYYY/MM') & CHR(39)
& ',' & CHR(39) & date(addmonths(today(),-3),'YYYY/MM') & CHR(39) & ')';

And then referencing the variable in your select:

SQL SELECT * FROM blahblah
WHERE YearMonth IN $(MonthList)

[#9]

I got everything working the way that Rob suggested. But your post raised this question, is the system going to know how to handle the output when running the report in Jan?

Or would it give me non-existant data from 2010/12, 2010/11...

[johnw]

It'll work fine. QlikView is smart enough to know that when you subtract a month from a January 2010 date that you should get a December 2009 date.