Extract Year from Data/Time Field

[#1]

Hi,

I am currently extracting data from a database using an ODBC connection using SQL to bring in my results. 

One of the fields in the table opencall is called logdate. The logdate field represents the data in qlikview like: 01/01/2012 13:00:00.

I am looking to create a List Box which shows the following Years.

2010

2011

2012

Basically there should be only one year, however when I click on the Year (ie 2012), all dates from 2012 pop up on another list box, which just has the logdate field.  (Therefore clicking just the year 2012, will bring up everything from 01/01/2012 to today). 

Would someone be able to let me know how this can be done.

I am very new to qlikview.

Regards,

Jon Ditchfield

[swuehl]

So you created a year field from your logdate using year() function or used a field expression in your list box?

This will connect your years with your logdates, as I understand that's not what you want? Do you want a data island, years not connected to logdate?

[#3]

I have found out how to list all my year.  In one of list boxes, I have gone to Expression in Field type and entered the following expression.

=Timestamp(logdate,'YYYY').  However this pulls through as

2012

2012

2012

2011

2011

2010

2010

2010

2010

etc.

I basically only want a 2012, 2011 and 2010. But I want to be able to click 2012, and it picks up all dates where the year is 2012 (E.G. 01/01/2012, 31/05/2012).

[swuehl · Accepted solution]

the Timestamp() function is only formatting your distinct timestamp values as years, the underlying numerical values are still distinct, thus you get multiple entries. Use year() function instead.

[#5]

Hi swuehl,

Ive just done the timestamp, however originally the data I had was in the filed logdate is:

01/01/2010

01/05/2010

01/06/2011

01/08/2011

01/01/2012

01/03/2012

01/04/2012

What I want is a Listbox that shows only 2010, 2011 and 2012.  Therefore if you click 2010, you will see the dates from another Listbox which are 01/01/2010 and 01/05/2010.

[#6]

Thanks for your helf swuehl, I entered =year(logdate) and it gave me exactly what I wanted.

[SKB27]

I also wanted to take Year out the date. I wrote Year(SalesDate) as year. SalesDate being a date in MM/DD/YYYY format. But on loading Data my year column in coming blank. What could have gone wrong?

I saw in the Auto generated section, it was written:

Date(Date#([Sales Date},'M.D.YYYY')) AS [Sales Date]

Whats the problem? Please help