topic Re:Help with dates. in QlikView

Help with dates.

Thu, 24 Dec 2009 01:58:49 GMT

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?

Help with dates.

Thu, 24 Dec 2009 02:13:41 GMT

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

Good luck.

Re:Help with dates.

Thu, 24 Dec 2009 02:17:42 GMT

What format is the TODAY() output in?

Re:Help with dates.

Thu, 24 Dec 2009 02:36:41 GMT

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.

Re:Help with dates.

Thu, 24 Dec 2009 02:52:09 GMT

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.

Re:Help with dates.

Thu, 24 Dec 2009 03:19:20 GMT

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?

Re:Help with dates.

rwunderlich — Thu, 24 Dec 2009 03:22:20 GMT

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

Re:Help with dates.

johnw — Thu, 24 Dec 2009 03:42:05 GMT

(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)

Re:Help with dates.

Thu, 24 Dec 2009 03:52:12 GMT

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...

Re:Help with dates.

johnw — Thu, 24 Dec 2009 03:57:43 GMT

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.