Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Non-Gregorian calendars

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.

 

Julian.png

 

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.

 

Hijri.png

 

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.

 

Hebrew.png

 

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.

 

Shamsi.png

 

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.

 

French.png

 

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.

 

HIC

 

Further reading related to this topic:

Non-Gregorian calendars

Recipe for a 4-4-5 Calendar

Calendars

12 Comments
MVP & Luminary
MVP & Luminary

Good post HIC. Full of interesting technical facts and a reminder of the wide wonderful world we live in.

540 Views
Partner
Partner

Good stuff HIC. Thanks for posting

0 Likes
540 Views
Partner
Partner

Thx for sharing!

0 Likes
540 Views
shoaib986
Contributor

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



SAK
Shoaib Ahmed

540 Views
Not applicable

Excellent.

0 Likes
540 Views
dafnis14
Contributor III

Hi Henric,

The Hebrew calendar was such a gift for me (or rather for a customer )

I suggest to add the day of the month in Hebrew too:

Thanks for your great solution and ongoing amazing blogs!

  

MonthDayNoMonthDayHebrewName
1א
2ב
3ג
4ד
5ה
6ו
7ז
8ח
9ט
10י
11י"א
12י"ב
12י"ג
13י"ד
14ט"ו
15ט"ז
16י"ז
17י"ח
18י"ט
19כ
20כ"א
21כ"ב
22כ"ג
23כ"ד
24כ"ה
25כ"ו
26כ"ז
27כ"ח
28כ"ט
29ל
30ל"א
0 Likes
540 Views
YoussefBelloum
Esteemed Contributor

Excellent !

0 Likes
540 Views
jood_ahmad
Contributor II

Do you have QVW sample pleasae!

0 Likes
540 Views

You have script examples on Non-Gregorian calendars

HIC

540 Views
jood_ahmad
Contributor II

Hi hic‌,

I used that file and as the mentioned i specified my fact table but it doesn't work can you help me, please.

0 Likes
540 Views
jood_ahmad
Contributor II

this sample date from the date i tried upload the document but no option to do that

3.JPG

0 Likes
540 Views

In the script, I have two alternative methods to find MinDate and MaxDate. The second one does not seem to work, and as a result you have NULLs in the two variables:

3b.jpg

Either you delete the second MinMax Load statement, with the subsequent Let statements, or you change it to fit your field names:

Image1.png

0 Likes
540 Views