Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Extract Year from Data/Time Field

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

6 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

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

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.

Not applicable
Author

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.

Not applicable
Author

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

SKB27
Contributor
Contributor

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