topic Re: Fiscal Week Start Date is showing arbitrary date in QlikView

Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 18:30:09 GMT

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.

Re: Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 18:38:20 GMT

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;

Re: Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 18:42:11 GMT

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

Re: Fiscal Week Start Date is showing arbitrary date

veidlburkhard — Thu, 25 Feb 2016 18:42:25 GMT

Hi chandni,

Date(0) = 12/30/1899

so seems you are starting at 0 !?!

Burkhard

Re: Fiscal Week Start Date is showing arbitrary date

sunny_talwar — Thu, 25 Feb 2016 18:43:06 GMT

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

Re: Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 20:24:25 GMT

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.

Re: Fiscal Week Start Date is showing arbitrary date

sunny_talwar — Thu, 25 Feb 2016 20:46:35 GMT

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

Re: Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 20:48:43 GMT

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

Re: Fiscal Week Start Date is showing arbitrary date

sunny_talwar — Thu, 25 Feb 2016 20:49:56 GMT

Can you try this:

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

Re: Fiscal Week Start Date is showing arbitrary date

Anonymous — Thu, 25 Feb 2016 20:53:43 GMT

It worked!! Thank you soooo much!