Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Attaching qvw for refernce.
I have applied Year function on date field but not giving any value.
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
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
There is other date fields in different format like 'DD/MM/YYYY'. so what to do for those fields
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
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.
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
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,
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
Great observation, didn't saw that
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