Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

petersvendsen
New Contributor II

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

Re: Can not use Year(xxx) as Year

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

7 Replies

Re: Can not use Year(xxx) as Year

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

hrlinder
Honored Contributor

Re: Can not use Year(xxx) as Year

for the year see Marcus answer

for the quarter use

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

MVP
MVP

Re: Can not use Year(xxx) as Year

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

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

petersvendsen
New Contributor II

Re: Can not use Year(xxx) as Year

Hi Marcus,

It works, thanks.

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

Thanks in advance,

Peter

petersvendsen
New Contributor II

Re: Can not use Year(xxx) as Year

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

petersvendsen
New Contributor II

Re: Can not use Year(xxx) as Year

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

 

Re: Can not use Year(xxx) as Year

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

Community Browser