Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator III
Creator III

Calendar

Hi Community,

I'm facing one issue.

this is my script using for calendar......

Calendar Script:

FactTable:

Load * Inline [

Date

2014-01-01

];

Temp: //Using the fact to extract min and max date

LOAD MIN(Date) as minDate,

          Today() as maxDate

Resident FactTable;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Tables Temp, FactTable;

TempCalendar:

LOAD

  $(vMinDate) + IterNo() - 1 as Num,

  Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate

  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:

LOAD

  TempDate as OrderDate,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  Weekday(TempDate) as WeekDay,

  'Q' & ceil(month(TempDate) / 3) as Quarter,

  'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

  MonthName(TempDate) as MonthYear,

  Week(TempDate)&'-'&Year(TempDate) as WeekYear

Resident TempCalendar

Order By TempDate ASC;

DROP Table TempCalendar;

Here, 2017 year, maximum week showing 52, how come??

Maximum week should be 39...

if i click 52 week also i.e associating to 01-01-2017

Did i anything wrong ??, Please let me know.

Thanks in Advance.....

13 Replies
Anil_Babu_Samineni

Where you are seeing that. I am getting correct one only

Capture.PNG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulwalker
Creator III
Creator III
Author

How come??

PFA,

its_anandrjs

If you select Month Sep it will display max month 39 to you

See attached all seems correct and fine.

its_anandrjs

OP2.PNG

OP3.PNG

paulwalker
Creator III
Creator III
Author

if i select Month, what is the use of maximum week ???

If i make selection 2017 year, that should show.. Max week 39..   (if i select month what is the use of Week function ??)

Why it's coming 52 week, and i.e also 01-01-2017  ??

oscar_ortiz
Partner - Specialist
Partner - Specialist

Week 52 for 01/01/2017 is because it is associated to the last week of 2016.

hic‌  has some great posts on this subject...

Redefining the Week Start

Redefining the Week Numbers

Good luck!

Oscar

vvira1316
Specialist II
Specialist II

Friends,

[MasterCalendar]:
LOAD
DateID, // just in case
date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as CalDay,
week(DateID) as CalWeek,
month(DateID) as CalMonth, // simple month name; it is dual - numeric and text
dual(month(DateID) & '-' & year(DateID),
year(DateID) & num(month(DateID), '00')) as CalMonthYear, // Month-Year format, dual
year(DateID) as CalYear,
WeekDay(DateID) as CalWeekday,
WeekEnd(DateID, 0, 5) as CalWeekEnd,      // This change in syntax is accepted correctly in script
'Q' &
ceil(month(DateID)/3) as CalQuarter, // in format Q1, Q2, Q3, Q4
// dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID)) as MonthQuarter,
year(DateID) & ceil(month(DateID)/3) as CalYearQtr // Qn-Year, dual
// and whatever else you may want here...
RESIDENT Date_src;
Drop Table Date_src;

//But following is not getting accepted

[MasterCalendar]:
LOAD
DateID, // just in case
date(DateID) as CalDate, // it will be in format defined in your SET DateFormat=, or in your system format
day(DateID) as CalDay,
week(DateID, 6, 0) as CalWeek,

I tried defining Sunday as start day, 0 for broken week and '01/01/2017' as start day for the year.

01/01/2017 is showing up in week 52 of 2017 (So agreeing to what Paul is saying)

oscar_ortiz
Partner - Specialist
Partner - Specialist

An option for that depending upon how you want your calendar to display is to use the WeekYear() function to pull the Year to the corresponding week that your day falls under.

vvira1316
Specialist II
Specialist II

01012017 in Week52.PNG

Expression in Text

=week('01/01/2017', 6, 0)

As per help on week, it should take additional optional parameter. Not sure what the issue is.

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/w...