Ancient Gods and Modern Days - Qlik Community

hic · ‎2012-10-23

The use of the seven-day week is ancient. Signs are found in the old Greek, Indian, Persian, Babylonian, Jewish, Akkadian and Sumerian cultures. Most likely it was invented by the Sumerians around 4500 years ago.

The Sumerians named the week days after the celestial bodies: The sun, the moon and the five known planets. Since the planets had names after gods, some days were thus also named after gods. These names were then translated into Babylonian, then into ancient Greek. In both translations the corresponding local gods were used.

     Set DayNames='Nanna;Gugalanna;Enki;Enlil;Inanna;Ninurta;Utu'; // Sumerian
     Set DayNames='Sin;Nergal;Nabû;Marduk;Ishtar;Ninurta;Shamash'; // Babylonian
     Set DayNames='Σελήνης;Ἄρεως;Ἑρμοῦ;Διός;Ἀφροδίτης;Κρόνου; Ἡλίου'; // Greek
     Set DayNames='Moon;Ares;Hermes;Zeus;Aphrodite;Cronos;Sun'; // Greek

The Romans, however, did not initially use a seven-day week. But a decision by Emperor Constantine in AD 321 eventually established the seven-day week also for the Roman Empire. Once again the names of the gods were translated to their local counterparts:

Set DayNames='Lunae;Martis;Mercurii;Iovis;Veneris;Saturni;Solis'; // Latin

Hence, the Latin names of the gods were mostly translations of the Greek names, which in turn were translations of the Babylonian names, which go back to the Sumerians. The Latin names can still be recognized in most Romanic languages, e.g. in French:

Set DayNames='Lundi;Mardi;Mercredi;Jeudi;Vendredi;Samedi;Dimanche'; // French

The Germanic tribes in northern Europe started to use the seven-day week long before they converted to Christianity, so the day names, except sun day and moon day, have the names of the old Germanic gods: Tyr/Tiw, Odin/Wotan, Thor/Donar and Freyja/Frige.

But for Saturday, the day was not translated. It is still “Saturn’s” day in e.g. both Dutch and English. And in all Nordic languages it is the “Washing day”. Because that is what you were supposed to do on Saturdays.

In QlikView, you can customize the day names by changing the variable DayNames as I have done above. These values will then be used by the WeekDay() function. Alternatively, you can create a new variable, e.g. vDays, and use this in the following field definition:

Dual(Subfield('$(vDays)',';',WeekDay(Date)+1),WeekDay(Date)) as WeekDay

But on which weekday does the week start? And which week is the first week of the year? The ISO 8601 defines these things clearly:

The week starts on a Monday
Week no 1 is the first week of the year with four days or more.

This means that if Jan 1st is a Friday, then week no 1 starts Monday Jan 4th, and the first three days of the year belong to the last week of previous year. It also means that if Jan 1st is a Thursday, week 1 starts Dec 29th. The ISO 8601 is used in many countries, among them most European ones.

But in North America and in the Middle East, different conventions are used. Often Sunday is considered the first day of the week. And Jan 1st is in some countries always part of week 1. As a consequence, the first and last week of the year are often fractional.

The QlikView week functions all use ISO 8601. If you want to define these entities in a different way, do the following: Introduce two variables that define your new week system:

Set vFirstWeekdayOfWeek = 6 ; // 0=Mon, 1=Tue, 2=Wed, ... , 6=Sun. (For the U.S. = 6)
Set vMinDaysInWeek = 1; // Minimal number of days in week no 1. (For the U.S. = 1)

Then you can define your week fields as:

     Load *,
          Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFirstWeekdayOfWeek)),7)) as WeekDay,
          Ceil((Date-FirstDateOfWeekOne+1)/7) as WeekNumber,
          Date(WeekStart(Date-$(vFirstWeekdayOfWeek))+$(vFirstWeekdayOfWeek)) as WeekStart;
     Load *,
          Date(
               WeekStart(YearStart(Date)+$(vMinDaysInWeek)-$(vFirstWeekdayOfWeek)-1)
               +$(vFirstWeekdayOfWeek)) as FirstDateOfWeekOne, …

Bottom line: Define fields for week day and week number in your master calendar. And don't hesitate to change the environment variables if you want long day names or different day names.

HIC