topic Re: year function not working in QlikView

year function not working

Mon, 18 Jan 2016 14:38:51 GMT

Hi,

Attaching qvw for refernce.

I have applied Year function on date field but not giving any value.

Re: year function not working

sunny_talwar — Mon, 18 Jan 2016 14:40:47 GMT

Try the SET statement in the script to set the date format.

SET DateFormat=YYYY-MM-DD';

DimRelease:

LOAD

[Deployment Date],

[Actual Deployment Date]

FROM

[20161815715.xlsx]

(ooxml, embedded labels, table is RMS_Dashboard20161815715);

The problem is that your date is not read as date by QlikView. Once you specify the date format, it will be read properly and you will be able to use Year, Month, MonthName and other Date and Time functions.

HTH

Best,

Sunny

Re: year function not working

Mon, 18 Jan 2016 14:42:23 GMT

There is other date fields in different format like 'DD/MM/YYYY'. so what to do for those fields

Re: year function not working

sunny_talwar — Mon, 18 Jan 2016 14:43:33 GMT

Then I would use Date#() function:

DimRelease:

LOAD

[Deployment Date],

Date(Date#([Actual Deployment Date], 'YYYY-MM-DD'), 'YYYY-MM-DD') as [Actual Deployment Date]

FROM

[20161815715.xlsx]

(ooxml, embedded labels, table is RMS_Dashboard20161815715);

Look in QlikView's help section to see some examples on how Date#() helps QlikView understand the Date Format -> QlikView ‒ Date# - script and chart function

Re: year function not working

jonathandienst — Mon, 18 Jan 2016 14:46:55 GMT

Convert them with date# conversion functions, like this:

LOAD

Date(Date#(dateAsYYYYMMDD, 'YYYYMMDD')) As date1,

Date(Date#([dateAsD-M-Y], 'DD-MM-YYYY')) As date2,

...

Date#() interprets the string as a date using the specified format, Date() converts these to your default document date format.

Re: year function not working

Anonymous — Mon, 18 Jan 2016 14:47:29 GMT

your field [Actual Deployment Date] has a lenght of 11 characters! maybe there is a space at end

so either drop the space or use another function

with year(left([Actual Deployment Date],10)) it works

Re: year function not working

florentina_doga — Mon, 18 Jan 2016 14:48:25 GMT

the fiekd is text

use subfield([Actual Deployment Date],'-',1) as year,

subfield([Actual Deployment Date],'-',2) as month,

subfield([Actual Deployment Date],'-',3) as day,

Re: year function not working

miguelbraga — Mon, 18 Jan 2016 14:51:14 GMT

Hi Amit,

I think your field [Actual Deployment Date] is preceived as a text because it's right aligned. Try make a coerent field like this in the script:

date(date#([Actual Deployment Date], 'YYYY-MM-DD'), 'DD-MM-YYYY') as [Actual Deployment Date]

This way you can use your expression to get year like this:

=year([Actual Deployment Date])

Regards,

Re: year function not working

miguelbraga — Mon, 18 Jan 2016 14:53:15 GMT

Great observation, didn't saw that

Re: year function not working

Anonymous — Mon, 18 Jan 2016 14:57:23 GMT

as even the normal data function didnot work I thought that something different must be the problem

and it was the lenght which didnot match, so it didnot recognize as a date

thks

Re: year function not working

Mon, 18 Jan 2016 14:57:35 GMT

Thanks a lot