Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you try this:
Date(Date#('09/24/2016', 'MM/DD/YYYY'), 'MM/DD/YYYY') as FiscalWeekStartDate
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;
I edited the post. There's no comma in my script. I don't know why that date is showing up.
Hi chandni,
Date(0) = 12/30/1899
so seems you are starting at 0 !?!
Burkhard
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
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.
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
I changed it to MM/DD/YYYY. It's still showing 12/30/1899
Can you try this:
Date(Date#('09/24/2016', 'MM/DD/YYYY'), 'MM/DD/YYYY') as FiscalWeekStartDate
It worked!! Thank you soooo much!