The most common way to group days, months and years in the world today is the Gregorian calendar. However, there are also other types of calendars used around the globe. This post is about how to create a non-Gregorian calendar in Qlik Sense or QlikView.
The Gregorian calendar was introduced by pope Gregorius XIII in 1582. It is by far the most common calendar in the world today. Before this, the Julian calendar was used.
The Julian calendar with the Anno Domini era was used from around 500 AD, and is still today used by the Christian orthodox churches. The basic rules are well known: 365 days in a year, and a leap year with an additional day every fourth year.
But this results in too many leap years, so the calendar year slowly drifts off from the tropical year. Eventually we will have midsummer in May or even April. By 1582 the difference between the tropical year and the calendar year was 10 days. This problem was fixed by the introduction of the Gregorian calendar.
The Gregorian calendar has leap years every fourth year, just as the Julian, but it doesn’t have leap years every change of a century, even though these years fulfil the rule of "every 4th year". Only every 4th century change is a leap year, the other ones are not. Hence, the year 1900 was not a leap year in the Gregorian calendar, but it was in the Julian.
The Julian calendar is easy to recreate in a master calendar in a Qlik app. All you need to do is to generate all days in a four-year cycle and assign the appropriate months and day numbers.
But there are also other calendars, and to create these you need help tables; tables that list when the month or year starts, and tables with the names of the months or the week days.
The Hijri calendar – or Islamic calendar – is used by Muslims all over the world to determine the proper days for the annual fasting and to celebrate other Islamic holidays and festivals. The first year in the calendar was 622 AD during which the emigration of Muhammad from Mecca to Medina took place. This journey is known as the Hijra.
It is a purely lunar calendar, containing 12 months based on the motion of the moon. This means that the Hijri year is always shorter than the tropical year, and therefore it shifts with respect to the Gregorian calendar.
To create a Hijri calendar, you need a table containing the month starts expressed as Gregorian dates. From this, you can generate a Hijri master calendar for your data model. It is however important to understand that this calendar, like any other Hijri calendar based on calculation, only gives estimated dates. The calendar is not based on the actual sighting of the moon, which is required for the beginning of some of the months. To get a proper calendar for religious purposes you should contact your local Muslim scholar.
The Hebrew calendar is used today predominantly for Jewish religious observances. It is a lunisolar calendar with 12 months based on the motion of the moon. However, to prevent it from shifting with respect to the seasons, a leap month is inserted approximately every third year.
To create a Hebrew calendar, you need a table containing the month starts expressed as Gregorian dates. From this, you can generate a Hebrew master calendar for your data model.
The Shamsi calendar, also known as Persian calendar or the Jalaali Calendar, is the official calendar in Iran. It is a purely solar calendar, containing 12 months originally based on the zodiac constellations. The year always starts at the vernal equinox as seen from the Tehran horizon. This means that the Shamsi calendar never shifts with respect to the tropical year. Further, no rules for leap years are needed: Depending on when the vernal equinoxes occur, some years automatically become leap years, others not.
To create a Shamsi calendar, you need a table containing the vernal equinoxes expressed as Gregorian dates. From this, you can generate a Shamsi master calendar for your data model.
The French Republican calendar is a purely solar calendar, containing 12 months, each with 30 days. At the end of the year, there are 5 or 6 additional days. It originally started at the autumnal equinox as seen from the Paris horizon, but it is not clear whether the intent was to have the year always start at the autumnal equinox, and thereby solving the leap year question, or the intent was to use Gregorian-like leap year rules. However, it is possible to use the autumnal equinox to recreate this calendar also for our time.
So, to create a French Republican calendar, you need a table containing the autumnal equinoxes expressed as Gregorian dates. From this, you can generate a French Republican master calendar for your data model. Its practical use can be questioned, perhaps, but the poetic month names can make it worthwhile.
Scripts for all the above calendars can be found on Non-Gregorian calendars. The scripts all generate non-Gregorian dates and assign these to existing Gregorian dates.
You should see these scripts as templates and examples of how to include a non-Gregorian calendar in your app, but don't trust the content too much - there may still be errors. Change the input data, if needed, and use scripts as models for yet additional calendars.