Custom Time hierarchies
eazyBI automatically creates two hierarchies in the Time dimension - default and weekly.
For week numeration, eazyBI uses ISO standard, which means the first week of the year always has January 4.
You can add additional custom hierarchies to the Time dimension with different rules with the Add custom hierarchy option. There are three types of custom hierarchies possible - fiscal monthly, multiple weeks, and calculated. This option is available for users with account Owner, User Admin, or Data Admin user roles.
On this page:
Fiscal monthly hierarchy
You can create your custom Fiscal hierarchy by selecting the month your fiscal year starts and the Year's name.
The fiscal hierarchy has the same levels as the default hierarchy of Time dimension (Year, Quarter, Month, and Day); it is possible to create only one custom fiscal hierarchy in the account. Fiscal year and quarter names will be created based on fiscal year selection.
The option to specify the Fiscal year is available on Cloud and since eazyBI version 7.0.
Multiple weeks hierarchy
The other type of custom hierarchy allows the creation of new hierarchies of multiple weeks. You can choose one, two, three, or four-week periods and select a start date. The date determines from which date the multi-week period starts.
You can create one instance of each type of the multiple-week dimension (one, two, three, or four-week periods).
Calculated time hierarchy
The option to add calculated time hierarchies in the Time dimension is available on Cloud and since eazyBI version 7.0.
You can add calculated hierarchies with JavaScript code describing how the hierarchy should work.
- Set a unique name for the hierarchy [1].
- Specify the hierarchy's levels- Year, Quarter, Month, Week, and Day [2].
- The custom JavaScript code should calculate the numeric value of the display name value for each level of the date [3].
- Test the custom hierarchy on specific dates [4].
The JavaScript code should include the return
statement containing a numeric and a name value for each level. We expect to have positive numeric values with a position of each date within each hierarchy level. The name value should contain the unique member name. Use a Time
data type variable timeValue
to address any date.
The example creates a custom hierarchy using the calendar year, month, and day values. The hierarchy excludes the quarter level and will have only the following levels: Year, Month, and Day.
return { year: timeValue.getFullYear(), year_name: timeValue.getFullYear(), month: timeValue.getMonth() + 1, month_name: strftime("%B %Y", timeValue), day: timeValue.getDate(), day_name: strftime("%B %d %Y", timeValue) }
If you would like to apply different rules when calculating the hierarchy, you can override default calendar values by calculating each level's value. Use examples below as guides.
Monthly, with a different start date in the previous month
The code below allows you to create a hierarchy in which months start at a specific date in the previous month.
Levels used: Year, Quarter, Month, Day
const monthStartDay = 25; // Get calendar year and month. var year = timeValue.getFullYear(); var month = timeValue.getMonth(); // Calculate month, quarter, and year of a selected date with specified offset. var monthStartDate = new Date(year, month - 1, monthStartDay) var nextMonthStartDate = new Date(year, month, monthStartDay) if (timeValue >= nextMonthStartDate) { if (month == 11) { month = 0; year++ } else { month++ } monthStartDate = nextMonthStartDate } month++; const monthName = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]; const quarter = Math.ceil(month / 3); // Calculate the day in month using the offset. const dayInMonth = Math.ceil((timeValue.getTime() - monthStartDate.getTime()) / 86400000.0) + 1; return { year: year, year_name: year, quarter: quarter, quarter_name: "Q" + quarter + " " + year, month: month, month_name: monthName[month-1] + " " + year, day: dayInMonth, day_name: strftime("%b %d %Y", timeValue) }
Monthly, with a different start date in the current month
The following code will build a hierarchy where the month starts at a specific date in the current month.
Levels used: Year, Quarter, Month, Day
const monthStartDay = 5; // Get calendar year and month. var year = timeValue.getFullYear(); var month = timeValue.getMonth(); // Calculate month, quarter, and year of a selected date with specified offset. var monthStartDate = new Date(year, month - 1, monthStartDay) var nextMonthStartDate = new Date(year, month, monthStartDay) if (timeValue >= nextMonthStartDate) { monthStartDate = nextMonthStartDate; month++; } else { if (month == 0) { month = 12; year--; } } const monthName = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]; const quarter = Math.ceil(month / 3); // Calculate the day in month using the offset. const dayInMonth = Math.ceil((timeValue.getTime() - monthStartDate.getTime()) / 86400000.0) +1; return { year: year, year_name: year, quarter: quarter, quarter_name: "Q" + quarter + " " + year, month: month, month_name: monthName[month-1] + " " + year, day: dayInMonth, day_name: strftime("%b %d %Y", timeValue) }
Monthly, using a specific start date in each month
The code below allows you to create a hierarchy using the zodiac calendar as an example of specifying specific start dates of each month if they have the same pattern each year.
Levels used: Year, Month, Day
const zodiac = { 0: { startDate: 20, monthName: "♒ Aquarius" }, 1: { startDate: 19, monthName: "♓ Pisces" }, 2: { startDate: 21, monthName: "♈ Aries" }, 3: { startDate: 20, monthName: "♉ Taurus" }, 4: { startDate: 21, monthName: "♊ Gemini" }, 5: { startDate: 22, monthName: "♋ Cancer" }, 6: { startDate: 23, monthName: "♌ Leo" }, 7: { startDate: 23, monthName: "♍ Virgo" }, 8: { startDate: 23, monthName: "♎ Libra" }, 9: { startDate: 24, monthName: "♏ Scorpio" }, 10: { startDate: 22, monthName: "♐ Sagittarius" }, 11: { startDate: 22, monthName: "♑ Capricorn" }, } const timezoneoffset = timeValue.getTimezoneOffset(); var year = timeValue.getFullYear(); var month = timeValue.getMonth(); const previousMonth = month == 0 ? 11 : month - 1 // Calculate the month and year for each date based on month start dates. var monthStartDate = new Date(year, month - 1, zodiac[previousMonth].startDate, 0, - timezoneoffset) var nextMonthStartDate = new Date(year, month, zodiac[month].startDate, 0, - timezoneoffset) if (timeValue < nextMonthStartDate) { if (month == 0) { month = 11; year-- } else { month-- } } else { monthStartDate = nextMonthStartDate } const dayInMonth = Math.ceil((timeValue.getTime() - monthStartDate.getTime()) / 86400000.0) + 1; return { year: year, year_name: "Y " + year, month: month + 1, month_name: zodiac[month].monthName + " " + year, day: dayInMonth, day_name: strftime("%b %d %Y", timeValue) }
5-4-4 weekly
The hierarchy includes all levels and will be based on a week split by months and quarters using pattern 5-4-4. It will use Sunday as the starting day, and 1st January is always in the first week of the year.
Levels used: Year, Quarter, Month, Week, Day
var weekStartDate = new Date(timeValue); // Week starts on Sunday. weekStartDate.setDate(weekStartDate.getDate() - weekStartDate.getDay()); var year = timeValue.getFullYear(); var timezoneoffset = weekStartDate.getTimezoneOffset(); var nextYearStartDate = new Date(year + 1, 0, 1, 0, - timezoneoffset); var daysTillNextYear = (nextYearStartDate.getTime() - weekStartDate.getTime()) / 86400000.0; // 1st January is in the first week. // The last days of December that falls into the same week when 1st January are counted for the the first week of the next year. year = daysTillNextYear < 7 ? year + 1 : year; var yearStartDate = new Date(year, 0, 1); // 1st January should be in the first week. // Weeks starts on Sunday. yearStartDate.setDate(yearStartDate.getDate() - yearStartDate.getDay()); var dayInWeek = Math.ceil((timeValue.getTime() - weekStartDate.getTime()) / 86400000.0) + 1; var days = Math.ceil((weekStartDate.getTime() - yearStartDate.getTime()) / 86400000.0); var week = Math.floor(days / 7) + 1; // 13 weeks in quarter with monthly split 5 4 4. var quarter = Math.ceil(week / 13); if (quarter == 5) { quarter = 4; var month = 12 } else { var weekInQuarter = (week % 13) == 0 ? 13 : (week % 13); var monthInQuarter = weekInQuarter == 1 ? 1 : Math.ceil((weekInQuarter - 1) / 4); month = (quarter - 1) * 3 + monthInQuarter } const monthName = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]; return { year: year, year_name: "Y " + year, quarter: quarter, quarter_name: "Q" + quarter + " " + year, month: month, month_name: monthName[month - 1] + " " + year, week: week, week_name: "W" + week, day: dayInWeek, day_name: strftime("%b %d %Y", timeValue) }
Quadrimester (four-month quarters)
The hierarchy below uses the same levels as the standard hierarchy, with three four-month "quarters" each year.
Levels used: Year, Quarter, Month, Day
const monthName = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]; var month = timeValue.getMonth(); var year = timeValue.getFullYear(); const quarter = Math.ceil((month+1)/4); return { year: year, year_name: year, quarter: quarter, quarter_name: "Q" + quarter + " " + year, month: month + 1, month_name: monthName[month] + " " + year, day: timeValue.getDate(), day_name: strftime("%B %d %Y", timeValue) }
Year-Month-Day hierarchy with different Month names
This code will return the month name as 11.2024 instead of November 2024.
Levels used: Year, Month, Day
return { year: timeValue.getFullYear(), year_name: timeValue.getFullYear(), month: timeValue.getMonth() + 1, month_name: timeValue.getMonth() + 1 + "." + timeValue.getFullYear(), day: timeValue.getDate(), day_name: strftime("%B %d %Y", timeValue) }