Calendar

[paulwalker]

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.....

[Anil_Babu_Samineni]

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

[paulwalker]

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]

[paulwalker]

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]

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]

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]

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]

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...