How to set up a 4-4-5 calendar
1. Abstract
Board offers the possibility to create custom time entities in addition to the standard time entities to customize the calendar to specific business needs.
Our goal will be to build a retail calendar based on the scheme of a 4-4-5 quarter, which is a method of managing accounting periods, mainly adopted by some industries such as retail and manufacturing.
It divides a year into four quarters of 13 weeks, each grouped into two 4-week "months" and one 5-week "month". The longer "month" may be set as the first (5–4–4), second (4–5–4), or third (4–4–5) unit.
Its major advantage over a regular calendar is that each period is the same length and ends on the same day of the week, which is useful for planning manufacturing or work shifts.
2. Context
The standard calendar of a Board database contains at least the year and the month according to the Julian calendar. In addition, days, weeks, and quarters can be used; the quarters are based on the principle that a year is divided into 4 quarters of 3 calendar months each. A special feature here is the definition of a fiscal year, the beginning of which is not necessarily fixed at the 1st of January, but can also be the 1st of August, for example. The quarters of a Board calendar can then be linked to either the standard year or the fiscal year.
However, this would still leave us with quarters consisting of 3 calendar months each. The 4-4-5 scheme can therefore only be achieved with custom (user-defined) time entities.
In principle, the relationships between the required entities can of course also be made via mapping cubes. However, this would mean that the comfort of the time functions provided by Board (e.g. year-over-year comparisons) cannot be used.
This article will describe how we set up a 4-4-5 calendar in the most pragmatic way – defining a new “Month” and how to put it to use with Board’s time functions.
3. Content
3.1 Preliminary considerations
Just as we need to think about which entities and hierarchies we need in the Board data model to map the business requirements, we should also be clear about which time entities we need and how they relate to each other. This applies not only to the custom time entities but also to the standard time dimensions.
The first step in building a new Board data model is always to define the time range. That will look like this:
However, this is not yet sufficient for our purposes. Not to mention that only the relationship between Month and Year is usable, as the Fiscal Year has no members at this point; this can also be seen when looking at the time entities that have been generated by the system as a result.
Only Month and Year are populated. In addition, the Fiscal Year might not be usable in this form, as it would refer to the calendar month.
3.2 Entities needed
We can certainly assume that product sales are accounted on a daily basis. Thus, we’ll need the Day entity.
We will need the standard week in order to build up a relationship between Day and our custom time entity that will represent a custom “month”. So, we’ll need something like this:
This is a bit premature, but it is already an appropriate image.
3.3 Custom Time Entity Definition
As we have seen above, Board will provide some “spare” time entities that we can use to customise our database. All those begin with “_Time” — and we can redefine the Fiscal Year.
So, we’ll take one of those “free” dimensions and redefine it as “Calendar 4-4-5”. It shall be a parent to the standard week. The model in mind should be something like this:
After we have defined the new entity (code and description length, as well as sorting).
We can edit the relationship:
3.4 Populating the Custom Time Entities
Any newly designed entity can only be populated after their relationship to each other has been defined.
3.4.1 Considering the time functions
Any custom time entity that is to be accessible via Board's time functions must be provided with a mask that the system can use to refer to the year.
The Board manual says the following:
3.4.2 Defining the mask
So that our new entity can now be used with the time functions, we define a corresponding mask which could be something like this:
3.4.3 Creating Members and Relationships
Now that our mask is defined, we can push data into our custom time entity. Keep in mind to apply the mask structure to the according code. The following steps must be performed
Extract the entity “Day”
Modify the extracted CSV file according to the defined mask (each 3rd “month” should consist of 5 “weeks” if you want to create a 4-4-5 calendar)
Create a DataReader to reload the modified file
Check the relationships in your database’s time range
If you look at the relation between weeks and custom month “Calendar 4-4-5” for the last quarter of 2023 you will notice that the last month consists of 5 weeks (green area) whereas the first two months of the last quarter consist of 4 weeks each.
3.5 Deployment
Now, that we have built up the custom time dimension and set up the relationships including the mask — let’s see how it works.
We will be using a cube of sales quantities as an example that is dimensioned by Day, Customer, and Product:
This will now be put in a DataView to demonstrate the difference between custom month (Calendar 4-4-5 set as row entity) and real month. We have set up a date cube populated with the transaction dates for this.
We will show the use of the “Previous Year” function based on standard time compared to being based on our custom time dimension Calendar 4-4-5.
“PY Standard” is based on “Year” whereas “PY 4-4-5” is based on our custom time dimension (that’s where the mask comes into play).
You can set the appropriate reference in the time functions section of the according block:
The resulting DataView will look similar to this:
4. Conclusion
Custom time dimensions are extremely flexible when it comes to building custom calendars upon standard Gregorian calendar.
4-4-5 Calendar is only an example, there are a lot of other cases where these custom time entities can be used to fit specific business needs.
Comments
-
Thanks @Helmut Heimann for the insights on this article!
3