topic Re: Extract Year from Data/Time Field in QlikView

Extract Year from Data/Time Field

Wed, 01 Aug 2012 14:09:33 GMT

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

Re: Extract Year from Data/Time Field

swuehl — Wed, 01 Aug 2012 14:17:04 GMT

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?

Re: Extract Year from Data/Time Field

Wed, 01 Aug 2012 14:17:52 GMT

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

2011

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

Re: Extract Year from Data/Time Field

swuehl — Wed, 01 Aug 2012 14:19:20 GMT

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.

Re: Extract Year from Data/Time Field

Wed, 01 Aug 2012 14:21:03 GMT

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.

Re: Extract Year from Data/Time Field

Wed, 01 Aug 2012 14:25:18 GMT

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

Re: Extract Year from Data/Time Field

SKB27 — Thu, 11 Apr 2019 06:28:12 GMT

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