topic Re: Calendar issue in QlikView

Calendar

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calendar issue

Anil_Babu_Samineni — Mon, 25 Sep 2017 16:51:54 GMT

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

Re: Calendar issue

paulwalker — Mon, 25 Sep 2017 17:00:10 GMT

How come??

PFA,

Re: Calendar issue

its_anandrjs — Mon, 25 Sep 2017 17:10:49 GMT

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

See attached all seems correct and fine.

Re: Calendar issue

its_anandrjs — Mon, 25 Sep 2017 17:13:27 GMT

Re: Calendar issue

paulwalker — Mon, 25 Sep 2017 17:22:20 GMT

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

Re: Calendar issue

oscar_ortiz — Mon, 25 Sep 2017 19:08:17 GMT

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

Re: Calendar issue

vvira1316 — Mon, 25 Sep 2017 20:37:39 GMT

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

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)

Re: Calendar issue

oscar_ortiz — Mon, 25 Sep 2017 20:49:26 GMT

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.

Re: Calendar issue

vvira1316 — Mon, 25 Sep 2017 21:21:23 GMT

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/week.htm

Re: Calendar issue

Anil_Babu_Samineni — Tue, 26 Sep 2017 01:51:22 GMT

FYI I've added this to refine and correct values

SET FirstWeekDay=6;

Re: Calendar issue

paulwalker — Tue, 26 Sep 2017 02:20:50 GMT

Actually i saw this post, but i didn't notice..

Thank you

Re: Calendar

paulwalker — Tue, 26 Sep 2017 02:27:58 GMT

Nice Solution... !!

Re: Calendar

Anil_Babu_Samineni — Tue, 26 Sep 2017 02:36:36 GMT

You can flag suitable answer as correct and numerous helpful ...