article Non-Gregorian calendars in Member Articles

Non-Gregorian calendars

hic — Wed, 21 Sep 2022 17:07:35 GMT

There are many non-Gregorian calendars used in the world today. This page posts Qlik scripts for some of them.

The Julian calendar

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. The file JULN_Script.txt contains a script that does this.

The Hijri calendar

The Hijri calendar, or Islamic calendar, is a purely lunar calendar, containing 12 months based on the motion of the moon. This means that the Hijri year always is 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 (HIJR_Calendar.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Hijri calendar have the HIJR prefix.

This calendar, like any other Hijri calendar based on calculation, only gives an estimated date. The calendar is not based on the actual sighting of the moon, which is required for the beginning of some of the months. For a proper calendar you should contact your local Muslim scholar.

The Hebrew calendar

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 the year 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 (HEBR_Calendar.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Hebrew calendar have the HEBR prefix.

The Shamsi calendar

The Shamsi calendar, also known as Persian calendar or the Jalaali Calendar, is a purely solar calendar, containing 12 months 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 seasons.

To create a Shamsi calendar, you need a table containing the vernal equinoxes (EQNX_Equinoxes.txt). From this, you can generate the master calendar of your data model. Attached you will also find tables containing template month names and day names. All files for the Shamsi calendar have the SHMS prefix. You also need the file EQNX_Equinoxes.txt.

The French Republican calendar

The French Republican calendar, also called the French Revolutionary calendar, was the official calendar in France from 1793 to 1805. It is a purely solar calendar, containing 12 months with 30 days each, and an additional 5-6 days at the end of the year. In this version, the year always starts at the autumnal equinox as seen from the Paris horizon. This means that it never shifts with respect to the seasons.

To create a French Republican calendar, you need a table containing the autumnal equinoxes expressed as Gregorian dates (EQNX_Equinoxes.txt). From this, you can generate a French Republican master calendar for your data model. Attached you will also find tables containing month names and day names. All files for the French Republican calendar have the FREP prefix. You also need the file EQNX_Equinoxes.txt.

About the scripts

The scripts generate a number of fields, e.g. Year, Month, Day of month and full date. All fields are dual where applicable, so they are correctly sorted.

The algorithms used for these five calendars are not identical, but they are similar. The basic structure is:

Load the year starts (or month starts) as Gregorian dates from an external table. Or generate them from some deterministic rule.
Generate all Gregorian dates between one year start and the next.
Load non-Gregorian month names and other data from external tables, and join these onto the calendar using the appropriate key, e.g. non-Gregorian month number.

Script files are named XXXX_Script.txt. Each of the script files will run as it is if you include it in an empty app and place the other source files in the same folder as the app. To use the script in an existing app, you need to remove the mock-up fact table from the script, and maybe rename some of the fields.

The information in the source files have been compiled from different open sources. They may contain errors and QlikTech cannot assume any responsibility for such errors or what these errors may lead to when the data is used. Consider the files as templates for how to solve the challenge of implementing a non-Gregorian calendar. Use at your own risk and verify that the content meets the requirements that you have.

See also the corresponding blog post: Non-Gregorian calendars.

HIC

Re: Non-Gregorian calendars

ashfaq_haseeb — Wed, 20 Jul 2016 07:12:55 GMT

Thanks hic‌

Nice piece of work. A good collection.

Lot of people always struggle with this.

Regards

ASHFAQ

Re: Non-Gregorian calendars

Anonymous — Thu, 21 Jul 2016 00:39:19 GMT

Henric:

Outstanding !

Re: Non-Gregorian calendars

Thu, 21 Jul 2016 02:32:56 GMT

Great stuff!

Re: Non-Gregorian calendars

Thu, 21 Jul 2016 09:47:38 GMT

Thanks!

Re: Non-Gregorian calendars

Anonymous — Tue, 26 Jul 2016 14:13:47 GMT

Great Henric! Covering all aspects of a calendar.

I also did a simple Hijri Calendar a year back or so as below:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';

HMonths:
LOAD HijriMonth,
HijriMonthEn,
HijriMonthAr
FROM

(ooxml, embedded labels, table is Months);

HMonthStart:
LOAD GregorianDate,
HijriMonth,
HijriYear,
HijriDay
FROM

(ooxml, embedded labels, table is MonthStart)
Where GregorianDate<>Null();

MinMaxDate:
Load date(Min(GregorianDate)) as MinDate, today() as MaxDate resident HMonthStart;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Join (HMonthStart)
Load Date(recno()+$(vMinDate)) as GregorianDate Autogenerate vMaxDate - vMinDate;

HijriCalendar:
NoConcatenate Load GregorianDate,
If( IsNull( HijriMonth ), Peek( HijriMonth ), HijriMonth ) as HijriMonth,
If( IsNull( HijriYear ), Peek( HijriYear ), HijriYear ) as HijriYear,
If( IsNull( HijriDay ), Peek( HijriDay )+1, HijriDay ) as HijriDay
Resident HMonthStart
Order By GregorianDate ; /* so that above values can be propagated downwards */

Drop Table MinMaxDate, HMonthStart;

HybridCalendar:
Load Distinct GregorianDate,
Year(GregorianDate) as Year,
Month(GregorianDate) as Month,
//Year(Date)&'-'&Month(Date) as [YY-MMMM],
   Date(MonthStart(GregorianDate), 'YYYY-MMM') as YearMonth,
//QuarterName(GregorianDate) as QuarterFull,
   //'Q' & Ceil(Month(GregorianDate)/3) as Quarter,
   HijriDay,
HijriMonth,
HijriYear,
text(HijriDay&'/'&HijriMonth&'/'&HijriYear) as HijriDate
Resident HijriCalendar
Where HijriDay <=30;
Drop Table HijriCalendar;

DATA

(ooxml, embedded labels, table is Months);

(ooxml, embedded labels, table is MonthStart)

You need to update it every month as per moon sighting

RESULTANT TABLE

Re: Non-Gregorian calendars

user-azadi — Sat, 21 Sep 2019 20:12:38 GMT

Great, Thanks a lot...