year function not working

[#1]

     Hi,

Attaching qvw for refernce.

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

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

[jonathandienst]

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.

[Anonymous · Accepted solution]

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

[florentina_doga]

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,

[miguelbraga]

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,

MB

[miguelbraga]

Great observation, didn't saw that

[Anonymous]

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