topic Re: Error creating day number of year using DayNumberOfYear function in QlikView

Error creating day number of year using DayNumberOfYear function

Tue, 25 Mar 2014 12:31:48 GMT

Im trying to create the day number of year using DayNumberOfYear function. However, all my dates before year 2000 starts from 2nd of january, and the 1stof january gets the number 366. All days after and including year 2000 gets the right numbers. Is this an error, or am I not using the DayNumberOfYear function correctly.

The issue arised when was creating a master calendar from start date 1950.

Sample:

Date	DayNumberOfYear
01-01-1998	366
01-01-1999	366
01-01-2000	1
01-01-2001	1

Also I have noticed that 01-01-2000 also have DayNumerOfYear = 366

Can anyone help me on this?

Best regards

Philip

Re: Error creating day number of year using DayNumberOfYear function

alexandros17 — Tue, 25 Mar 2014 12:35:57 GMT

That function works with year based on 366 days (look at help)

Re: Error creating day number of year using DayNumberOfYear function

Tue, 25 Mar 2014 13:16:26 GMT

Alessandro Saccone: Thank you for that. So i guess the problem really is how do I get the DayNumberOfYear when I only have 364 days in a year?

The problem is that I have a dataset where the date for an entry is YYYDDD and the largest DDD number is 364.

Re: Re: Error creating day number of year using DayNumberOfYear function

Nicole-Smith — Tue, 25 Mar 2014 13:26:42 GMT

Assign the day numbers in the script. Something like this:

let MinDate = num(makedate(1998,1,1));

let MaxDate = num(makedate(2003,1,1));

Data:

Load Date, if(Month(Date)=1 and Day(Date)=1, 1, PEEK('DayNumberOfYear')+1) as DayNumberOfYear;

Load Date($(MinDate)-1+iterno()) as Date autogenerate 1 While iterno() <= $(MaxDate)+1 - $(MinDate);

let MinDate = null();

let MaxDate = null();

Example file is also attached.

Re: Error creating day number of year using DayNumberOfYear function

Tue, 25 Mar 2014 13:54:20 GMT

@Nicole Smith: Thank you very much

Re: Error creating day number of year using DayNumberOfYear function

Fri, 28 Mar 2014 06:23:37 GMT

NOTE: The date format YYYYDDD comes from IBM DB2 databases, in case anyone need it.