Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chandni90
New Contributor III

Fiscal Week Start Date is showing arbitrary date

Hi,

Please see the script below for Calender Data:

LET vDateMin = Num(MakeDate(2005,9,26));

LET vDateMax = Num(MakeDate(2015,9,25));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS CalendarDate,

num(Date(TempDate)) AS numDate,

WeekDay(TempDate) as WeekDay,

Week(TempDate) AS CalendarWeek,

Right(WeekName(TempDate,13,-2),2) as FiscalWeek,

Year(TempDate) AS CalendarYear,

num(Month(TempDate)) AS CalendarMonth,

WeekStart(TempDate,00,-2) as FiscalWeekStartDate

RESIDENT TempCalendar ORDER BY TempDate ASC;

I am not sure where is 12/30/1899 coming from. Please help.

calendar.png

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Fiscal Week Start Date is showing arbitrary date

Can you try this:

Date(Date#('09/24/2016', 'MM/DD/YYYY'), 'MM/DD/YYYY') as FiscalWeekStartDate

9 Replies

Re: Fiscal Week Start Date is showing arbitrary date

I tried your script, and apart from the unwanted comma after FiscalWeekStartDate it works fine for me, after I deleted the comma.

WeekStart(TempDate,00,-2) as FiscalWeekStartDate,

RESIDENT TempCalendar ORDER BY TempDate ASC;

chandni90
New Contributor III

Re: Fiscal Week Start Date is showing arbitrary date

I edited the post. There's no comma in my script. I don't know why that date is showing up.

veidlburkhard
Contributor III

Re: Fiscal Week Start Date is showing arbitrary date

Hi chandni,

Date(0) = 12/30/1899

so seems you are starting at 0 !?!

Burkhard

MVP
MVP

Re: Fiscal Week Start Date is showing arbitrary date

I second that. No issues found. Is this the way you are setting variables in your app or this was just for demo purposes and you have variable setting done using dates in your application? My guess is that 12/30/1899 is a 0 date

chandni90
New Contributor III

Re: Fiscal Week Start Date is showing arbitrary date

I am sorry for the confusion. Since we have 53 weeks in this year, I've added this code:

LET vDateMin = Num(MakeDate(2016,09,24));

LET vDateMax = Num(MakeDate(2016,09,30));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Concatenate (Calendar)

LOAD

Date(TempDate) AS CalendarDate,

num(Date(TempDate)) AS numDate,

WeekDay(TempDate) as WeekDay,

Week(TempDate) AS CalendarWeek,

'53' as FiscalWeek,

Year(TempDate) AS CalendarYear,

num(Month(TempDate)) AS CalendarMonth,

'13' as WeekOfFiscalQuarter,

'4' as WeekOfFiscalMonth,

'3' as MonthOfFiscalQuarter,

YearName(TempDate,0) as FiscalYear,

'4' as FiscalQuarter,


'12' as FiscalMonth,         

date(09/24/2016,'mm/dd/yyyy') as FiscalWeekStartDate

RESIDENT TempCalendar  ORDER BY TempDate ASC;

Instead of 09/24/2016, it's giving me this wrong date for week 53.calendar.png

MVP
MVP

Re: Fiscal Week Start Date is showing arbitrary date

I think you need to change mm to MM. mm is for minutes and MM is for Months

date(09/24/2016,'MM/dd/yyyy') as FiscalWeekStartDate

chandni90
New Contributor III

Re: Fiscal Week Start Date is showing arbitrary date

I changed it to MM/DD/YYYY. It's still showing 12/30/1899

Highlighted
MVP
MVP

Re: Fiscal Week Start Date is showing arbitrary date

Can you try this:

Date(Date#('09/24/2016', 'MM/DD/YYYY'), 'MM/DD/YYYY') as FiscalWeekStartDate

chandni90
New Contributor III

Re: Fiscal Week Start Date is showing arbitrary date

It worked!! Thank you soooo much!