Redefining the Week Start - Qlik Community

hic · ‎2014-01-21

Six days thou shalt work, but on the seventh day thou shalt rest.

[Exodus 34:21]

The idea that you should rest on the seventh day is a central concept in both Christianity and Judaism. But which weekday is the seventh day of the week? And which day is the first?

The old texts of the Abrahamic religions clearly consider the Sabbath – Saturday – as the seventh day of the week. This day is also still today the resting day for Jews around the world. The same texts also describe how Adam was created on the sixth day, which is one of the reasons why Friday is the day of congregation and prayers for Muslims.

Hence, these two religions agree on the numbering of week days: Friday is the sixth day of the week and Saturday is the seventh.

However, in the rest of the world, the situation is more confusing: Although Sunday is observed as resting day in most countries, there is a disagreement on whether Sunday is the first or the seventh day of the week. In North America, Sunday is the first day of the week, but in many European countries it is the last day of the week. According to the International Organization for Standardization (ISO 8601), the week starts on a Monday, and Sunday is thus the seventh and last day of the week.

How weekdays are ordered makes a difference in all Business Intelligence applications, most notably in how list boxes and charts are sorted. Note the order of the week days in the list boxes below. In the left one, Sunday is on top of the list and in the right one it is Monday.

This difference can also be seen in calendar displays, used in many types of software and on many sites in the web. Again, note the order of the week days.

QlikView uses ISO 8601 to define the week days and the week start. WeekDay() returns the name of the week day (as defined in the variable DayNames) and a number from 0 to 6; Monday being day number 0. WeekStart() returns the date of the Monday immediately before the date supplied as parameter.

If you want redefine this, e.g. if you want QlikView to show Sunday as the first day of the week – here’s how you do it:

Start by creating a variable that defines the beginning of the week:

Set vCal_FD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

The WeekStart() function has an offset parameter, and if you use your variable here, you can redefine how the function works and get the week start on the correct day:

WeekStart( Date, 0, $(vCal_FD) ) as WeekStart

The WeekDay() function, however, cannot take any offset parameter, so you need to define the week day using the Dual() function:

Dual( WeekDay( Date ), Mod( WeekDay( Date - $(vCal_FD) ), 7 ) +1 ) as WeekDay

Using these two expressions in your Master Calendar instead of the standard function calls, you can redefine the week start to any of the week days.

HIC

Further reading related to this topic:

Ancient Gods and Modern Days

Redefining the Week Numbers

Redefining the Week Start in Qlik Sense

narband2778 · ‎2014-01-21

interesting....

thanks for posting....

Clever_Anjos · ‎2014-01-21

Thank you Henric, very enlightening as usual.

Report Inappropriate Content · ‎2014-01-21

Thank you Henric. You have helped me out with questions I had on Master Calendar before. I have it in different responses and want to put it all together. I was wondering if you could help me out providing me an example that combines all the steps together when creating a Master Calendar. The best way, variables, etc. I am going to give you the scenario and if you can fill in the gaps that would be awesome. I have included some comments about the field in bold but they are not in script.

GOAL: To have three charts. One for each of the Date Fields from my Load Statement. The charts would be how many new IP's were created, how many IP's had their first training session, and how many IP's did we have go live. We might have 10 IP's created, 15 IP's had their first training session, and 4 IP's went live. The 15 IP's is strictly looking at the "Initial_Training_Scheduled_For__c" so it is capturing the actual number of trained IP's for that month regardless of when those IP's we created.

I hope this makes sense.

Here is my Load Statement:

Accounts:

LOAD Id as AccountId,

Name as OrganizationName,

Industry

FROM

Account.qvd

(qvd);

ImplementationPlans:

LOAD Id as IPID,

CreatedDate, //This is the date the IP was Created//

Initial_Training_Scheduled_For__c, //This is the date the IP was first Trained//

Go_Live_Date__c //This is the date the IP was Live//

FROM

Implementation_Plan.qvd

(qvd);

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS CreatedDate,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS Initial_Training_Scheduled_For__c,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

MasterCalendarIPLive:

LET vDateMin = Num(MakeDate(2012,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS Go_Live_Date__c,

Month(TempDate) AS CalendarMonth,

Year(TempDate) AS CalendarYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

I believe I have correct syntax for the above Load Statement. I have included a copy of my Dashboard view to help you out.

1. I created a ListBox for Industry ( I used the Industry Field)

2. I created a ListBox for Years (I used the CalendarYear Field)

3. I created a ListBox for Months (I used the CalendarMonth Field)

4. The charts are for the three date fields I want to return results on:

a. Total IP's Created

b. Total IP's First Training

c. Total IP's Live

This is where I am still a little confused. How do I show results based on The Year, The Month, or Both depending on what options are selected? My guess is through the Dimension and the Expression on each of the charts but not sure what it would look like. I have tried different ones and must be using the wrong fields or calculations. Can you give me some insight?

Thanks

David

stevedark · ‎2014-01-21

Thanks for posting Henric.

The other difference that comes about is what WeekNumber any given date falls into. I believe to resolve this shifting the date you are checking for by the correct number of days can shift week number correctly, eg:

Week(Date+1) as [Week No],

WeekYear(Date+1) & '-' & Week(Date+1) as [Year Week],

As you say, encapsulating this into your Master Calendar makes a lot of sense.

The one could be replaced by a calculation based on your First Day variable as well, if required.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Anonymous · ‎2014-01-21

Thanks once more HIC.

Dates are always a problematic data.

CB.

hic · ‎2014-01-21

Steve

The problem around Week Numbers will come in an other post...

HIC

stevedark · ‎2014-01-21

I shall look out for that one!

Report Inappropriate Content · ‎2014-01-21

Bookmarked!! thanks HIC

Report Inappropriate Content · ‎2014-01-21

Hi HIC,

I posted my question to the wrong post. I have added my question to the Master Calendar question I had out there already. If you can respond to that one instead that would be awesome.

David

sudeepkm · ‎2014-01-22

Very nice and interesting post with a great background. Thanks a lot HIC.