Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Help with dates.

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?

Tags (2)
1 Solution

Accepted Solutions

Re:Help with dates.

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

9 Replies
Not applicable

Help with dates.

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

Good luck.

Not applicable

Re:Help with dates.

What format is the TODAY() output in?

Not applicable

Re:Help with dates.

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.

Not applicable

Re:Help with dates.

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.

Not applicable

Re:Help with dates.

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.

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

MVP
MVP

Re:Help with dates.

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

Not applicable

Re:Help with dates.

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

MVP
MVP

Re:Help with dates.

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.