Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
SilviyaK
Contributor III
Contributor III

Custom Calendar, Fiscal Week is wrong

Hi

I have the following calendar:

LET vDateMin = Num(MakeDate(2018,10,1));
LET vDateMax = Floor(AddMonths(YearEnd(AddMonths(Today(),5)),100));

CalendarDates:
LOAD
$(vDateMin) + RowNo() - 1 as DateNumber
, Date($(vDateMin) + RowNo() - 1) as TempDate
, Date($(vDateToday)) as DateToday
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:
LOAD
TempDate as %Key_Date
, TempDate
, Num(Week(TempDate),00) as Calendar_Week_Number
, Month(TempDate) as Calendar_Month
, Num(Month(TempDate),00) as Calendar_Month_Number
, Year(TempDate) as Calendar_Year
, Year(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as Calendar_Quarter
, WeekYear(TempDate) & 'W' & Num(Week(TempDate),00) as Calendar_Year_Week
, Year(TempDate) & '-' & Num(Month(TempDate),00) as Calendar_Year_Month
, Year(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as Calendar_Year_Quarter
, Num(Month(AddMonths(TempDate,3)),00) as Fiscal_Month_Number
, Month(AddMonths(TempDate,3)) as Fiscal_Month
, Week(AddMonths(TempDate,3)) as Fiscal_Week
, weekyear(AddMonths(TempDate,3)) & 'WK' & Num(Week(AddMonths(TempDate,3)),00) as Fiscal_Year_Week
, Year(AddMonths(TempDate,3)) as Fiscal_Year
, Year(AddMonths(TempDate,3)) & 'Q' & Ceil(Month(AddMonths(TempDate,3))/3) as Fiscal_Quarter
, Year(DateToday) as Fiscal_Year_Today

It works great, except for this field:

weekyear(AddMonths(TempDate,3)) & 'WK' & Num(Week(AddMonths(TempDate,3)),00) as Fiscal_Year_Week

It almost gets the weeks number correctly, but the week starts on Wednesday.

Any ideas what I am doing wrong? 

Labels (1)
0 Replies