Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Non-Gregorian calendars

Employee
Employee

Non-Gregorian calendars

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:

  1. Load the year starts (or month starts) as Gregorian dates from an external table. Or generate them from some deterministic rule.
  2. Generate all Gregorian dates between one year start and the next.
  3. 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

Labels (2)
Attachments
Comments

Thanks hic

Nice piece of work. A good collection.

Lot of people always struggle with this.

Regards

ASHFAQ

carbal1952
Contributor II

Henric:

Outstanding !

Not applicable

Great stuff!

Not applicable

Thanks!

shoaib986
New Contributor III

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





Version history
Revision #:
1 of 1
Last update:
‎07-18-2016 06:22 AM
Updated by:
Employee