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

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
sunny_talwar

Can you try this:

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

View solution in original post

9 Replies
Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

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

veidlburkhard
Creator III
Creator III

Hi chandni,

Date(0) = 12/30/1899

so seems you are starting at 0 !?!

Burkhard

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

Can you try this:

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

Anonymous
Not applicable
Author

It worked!! Thank you soooo much!