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: 
Anonymous
Not applicable

Can not use Year(xxx) as Year

Dear all,

I'm new within QlikView and are testing the application.

I have tried to load with the following script

...................

SET DateFormat='MMM DD, YYYY';

................... 

LOAD [No. (Ticket)],
               [Department (resp.)],
               [Created Date],
               Year([Created Date]) as Year,
               Month([Created Date]) as Month,

...................

my .xls file contain the coulmb Create Date e.g with the following date Jul 22, 2015

When I reload my data all goes well but the month and year are always empty... why is that?

Another question, is there a Quarter() function as well so I can convert a date to a quarter? I'm not able to locate it?

Thanks in advance

Peter

1 Solution

Accepted Solutions
marcus_sommer

The reason will be that your [Created Date] won't be recognized as date. You need some convert- and fromat-functions for this, try:

year(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY')) as Year

- Marcus

View solution in original post

7 Replies
marcus_sommer

The reason will be that your [Created Date] won't be recognized as date. You need some convert- and fromat-functions for this, try:

year(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY')) as Year

- Marcus

Anonymous
Not applicable
Author

for the year see Marcus answer

for the quarter use

'Q' & ceil(month(Created Date) / 3)) AS Quarter

maxgro
MVP
MVP

for quarter, when you a have a date field, you can use

'Q' & Ceil(Month([Created Date])/3) as Quarter

Anonymous
Not applicable
Author

Hi Marcus,

It works, thanks.

Could you shortly explain me how it works / how I should read it?

Thanks in advance,

Peter

Anonymous
Not applicable
Author

Hi Rudolf and Massimo

It seems that your proposal also works or partly because I only get "Q" as a result.. I was expecting Q1, Q2, Q3 and Q4 as result.

Could you shortly explain me how it works / how I should read it and what the reason may be that I dont get the expected result.

Thanks in advance,

Peter

Anonymous
Not applicable
Author

Hi again,

Found the problem, I ofcause mneed to take the input from Marcus as well into the script

'Q' & Ceil(Month(date(date#([Created Date], 'MMM DD, YYYY'), 'MMM DD, YYYY'))/3) as Quarter,

Best regards

Peter

 

marcus_sommer

Hi Peter,

a date is in real a number, starting with 1 on 01.01.1900 and times are parts from 1, for example 18:00 / 24 h = 0,75. QlikView will interprets date- and time-fields in consideration to the SET variables at the beginning from each script or if they are missing defined through the regional-settings from OS.

If you now loads date-fields with another formatting you need to tell qlikview thats are dates and how it should regocnize them. This will be done per date#(value, format) which returned if the formatting is correct a numeric value which then could be formated in any way you like - this field is then a dual-field with a string-representation from your choosen format and contains as numeric value the day-number counted up from 01.01.1900.

Many more information abouts dates could you find here: How to use - Master-Calendar and Date-Values

- Marcus