Custom time hierarchies
flex.bi automatically creates two hierarchies in the "Time" dimension - default and weekly.
For week numeration, flex.bi 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.
Fiscal monthly hierarchy
You can create your custom Fiscal hierarchy by selecting the month when your fiscal year starts and naming for Year.
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.
Multiple weeks hierarchy
The other type of custom hierarchy allows the creation of new hierarchies of multiple weeks. You can choose the option of one, two, three, or four-week periods and select a date. The date determines from which day the week count is started.
You can create one instance of each type of the multiple-week dimension (one, two, three, or four-week periods).
Calculated time hierarchy
You can add calculated hierarchies with JavaScript code describing how the hierarchy should work.
Set the unique hierarchy name for your calculated hierarchy [1].
Specify the hierarchy's levels as a selection from predefined 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 value and 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 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 would like to override default calendar values and calculate each level's value. Use some of the examples below as guides.
Monthly with a different start date
The hierarchy below will include levels Year, Quarter, Month, Day where the month starts at a specific date in the previous month.
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 using a specific start date in each month
The hierarchy below uses the zodiac calendar as an example of how to specify specific start dates of each month if they have the same pattern each year:
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 week split by month - quarter using pattern 5-4-4. It will use Sunday as a starting day, and 1st January is always in W1.
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.
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)
}