Dimension.Date

The below code is used to create a Date dimension.

Process Code

var dim = "Date";

var hierDefault = "Default";
var hierDateList = "Date List"
var hierNoDate = "No Date"
var hierCTD = "Cumulative to Date"
var hierYTD = "Year to Date";
var hierMonthYTD = "Month Year to Date"
var hierMonth12M = "Rolling 12 Months";
var hierMTD = "Month to Date"
var hierMonthAssumption = "Month Assumption";
var hierWeekList = "Week List"

var hiers = [hierDefault, hierDateList, hierWeekList, hierNoDate, hierCTD, hierYTD, hierMonthYTD, hierMonth12M, hierMTD, hierMonthAssumption]

var dateFrom = null;
var dateTo = null;
var monthsCal = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

function pre() {
    //this function is called once before the processes is executed.
    //Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');

    var yearFrom = ""
    var yearTo = ""

    let rootElms = hierarchy.rootElements("Time", "Calender Year List");
    if (canBeArray(rootElms)) {//there are elms returned from rootElms
        rootElms = JSON.parse(rootElms);
        yearFrom = rootElms[0].substring(2);
        yearTo = (Number(rootElms[rootElms.length - 1].substring(2)) + 1).toString() //going through to 2026 means we only have months up to 2025 and thus need to add a month to get to what period we want
    } else {
        var d = new Date();
        yearFrom = (d.getFullYear() - 3) + "";
        yearTo = (d.getFullYear() + 1) + "";
    }


    script.prompt("Year From", "yearFrom", yearFrom);
    script.prompt("Year To", "yearTo", yearTo);


}

function begin() {
    //this function is called once at the start of the process

    script.log('process execution started.');
    dimension.createOrWipe(dim, "Standard");

    for (let hier of hiers) {
        hierarchy.createOrWipe(dim, hier)
    }

    hierarchy.group(dim, hierNoDate, "", hierNoDate);


    let dateVariableFrom = yearFrom + "-01-01"

    let variableTo = (Number(yearTo) - 1).toString() + "-12-31"

    if (dateVariableFrom == "") {
        console.log("The variable \"model.Time.Start\" used to denote the start of the date dimension is not populated.");
        script.abort("The variable \"model.Time.Start\" used to denote the start of the date dimension is not populated.");
    }

    if (variableTo == "") {
        console.log("The variable \"model.Time.Finish\" used to denote the start of the date dimension is not populated.");
        script.abort("The variable \"model.Time.Finish\" used to denote the start of the date dimension is not populated.");
    }



    dateFrom = new Date(dateVariableFrom);
    dateTo = new Date(variableTo);
    console.log(dateVariableFrom, variableTo)

    alias.createOrWipe(dim, "Week starting");

    let dateListing = getDates(dateFrom, dateTo);
    let YTDStr = "";
    let YTDItems = [];
    let TTDItems = [];

    let MTDStr = "";
    let MTDItems = [];
    var weekAlias = "";


    for (let i = 0; i < dateListing.length; i++) {
        let sDate = dateListing[i]
        let yearString = sDate.getFullYear() + "";
        let monthString = sDate.getMonthName();
        let dateString = sDate.getName();
        let finYearString = yearString;

        hierarchy.structure(dim, hierMonthAssumption, "All Month Assumptions", finYearString + " Assumption")

        hierarchy.group(dim, hierMonthAssumption, finYearString + " Assumption", monthString + " Assumption")


 

        hierarchy.group(dim, hierDateList, "", dateString);

        hierarchy.structure(dim, hierDefault, "All Dates", finYearString);
        hierarchy.structure(dim, hierDefault, finYearString, monthString);
        hierarchy.group(dim, hierDefault, monthString, dateString);



        //update weeks
        let weekString = sDate.getFullYear() + " - Wk " + sDate.getWeek();
        let weekYearString = yearString;
        let clearWeek;

        if (sDate.getWeek() == 53 && yearString != "2022") {
            //belongs to the next year.
            if (sDate.getFullYear() + 1 <= dateListing[dateListing.length - 1].getFullYear()) {
                weekString = sDate.getFullYear() + 1 + " - Wk 1";
                weekYearString = (sDate.getFullYear() + 1) + "";
            } else {
                //week doesnt exist in our date dimension
                weekString = null;
            }
        }

        if (sDate.getWeek() == 0) {
            //belongs to the next year.
            weekString = sDate.getFullYear() - 1 + " - Wk 53";
            weekYearString = (sDate.getFullYear() - 1) + "";
        }

        TTDItems.push(dateString);

        if (weekString != null) {
            hierarchy.structure(dim, "Week List", "All Weeks", weekString);
            //hierarchy.structure(dim, "Week List", "WK-" + weekYearString, weekString);
            hierarchy.group(dim, "Week List", weekString, dateString);
            if (hierarchy.childByIndex(dim, "Week List", weekString, 0) == dateString) {
                weekAlias = "WS " + dateString;
                alias.set(dim, "Week starting", weekString, weekAlias);
            }

        }


        // Get the starting month and year for grouping
        let startYear = sDate.getFullYear();
        let startMonth = sDate.getMonth(); // 0-based index for months

        // Loop through 12 months
        for (let j = 0; j < 12; j++) {
            // Calculate the current month and year for the group
            let groupYear = startYear;
            let groupMonth = startMonth + j;

            // Handle year overflow when month exceeds 11
            if (groupMonth > 11) {
                groupMonth -= 12;
                groupYear += 1;
            }

            // Generate the group name in the format "YYYY - MMM"
            let groupDate = new Date(groupYear, groupMonth, 1); // Create a date for this group
            let groupName = `${groupDate.getMonthName()} L12M`;

            // Add the sDate to the group

            let parentDate = new Date(dateString);
            let parentElm = parentDate.getMonthName()
            hierarchy.structure(dim, hierMonth12M, groupName, parentElm);
            hierarchy.group(dim, hierMonth12M, parentElm, dateString);

        }

        //update YTD's
        if (YTDStr != yearString) {
            YTDItems = [];
        }
        YTDStr = yearString;
        YTDItems.push(dateString);

        for (let k = 0; k < YTDItems.length; k++) {
            hierarchy.group(dim, hierYTD, dateString + " - YTD", YTDItems[k]);
        }

        for (let k = 0; k < TTDItems.length; k++) {
            hierarchy.group(dim, hierCTD, dateString + " - CTD", TTDItems[k]);
        }

        if (MTDStr != monthString) {
            MTDItems = [];
        }
        MTDStr = monthString;
        MTDItems.push(dateString);
        for (let k = 0; k < MTDItems.length; k++) {
            hierarchy.group(dim, hierMTD, dateString + " - MTD", YTDItems[k]);
        }

        for (let k = 0; k < YTDItems.length; k++) {
            hierarchy.group(dim, hierMonthYTD, MTDStr + " - YTD", YTDItems[k]);
        }
        if (i % 250 == 0) {
            console.log(`Dates Processed: ${i}`)
        }

    }

}

function data(record) {
    //this function is called once for each line of data on the second cycle
    //use this to build dimensions and push data into cubes

}

function end() {
    //this function is called once at the end of the process
    script.log('process execution finished.');

}
/*
Date.prototype.getWeekNumber = function(){
  var d = new Date(Date.UTC(this.getFullYear(), this.getMonth(), this.getDate()));
  var dayNum = d.getUTCDay() || 7;
  d.setUTCDate(d.getUTCDate() + 4 - dayNum);
  var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
  return Math.ceil((((d - yearStart) / 86400000) + 1)/7)
};
*/
Date.prototype.addDays = function (days) {
    var dat = new Date(this.valueOf())
    dat.setDate(dat.getDate() + days);
    return dat;
}
Date.prototype.removeDays = function (days) {
    var dat = new Date(this.valueOf())
    dat.setDate(dat.getDate() - days);
    return dat;
}
Date.prototype.getWeek = function () {
    var onejan = new Date(this.getFullYear(), 0, 1);
    return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()) / 7);
}

Date.prototype.getName = function () {
    let yr = this.getFullYear();
    let mth = this.getMonth() + 1;
    let da = this.getDate();

    let mthStr = mth + "";
    if (mthStr.length == 1) {
        mthStr = "0" + mthStr;
    }

    let daStr = da + "";
    if (daStr.length == 1) {
        daStr = "0" + daStr;
    }

    return yr + "-" + mthStr + "-" + daStr;

}

Date.prototype.getMonthName = function () {
    let yr = this.getFullYear();
    let mth = monthsCal[this.getMonth()];
    return yr + " - " + mth;
}


Date.prototype.getName = function () {
    let yr = this.getFullYear();
    let mth = this.getMonth() + 1;
    let da = this.getDate();

    let mthStr = mth + "";
    if (mthStr.length == 1) {
        mthStr = "0" + mthStr;
    }

    let daStr = da + "";
    if (daStr.length == 1) {
        daStr = "0" + daStr;
    }

    return yr + "-" + mthStr + "-" + daStr;

}

function getDates(startDate, stopDate) {
    var dateArray = new Array();
    var currentDate = startDate;
    while (currentDate <= stopDate) {
        dateArray.push(currentDate)
        currentDate = currentDate.addDays(1);
    }
    return dateArray;
}



function canBeArray(input) {
    try {
        // Attempt to parse the input as JSON
        const parsed = JSON.parse(input);

        // Check if the parsed value is an array
        return Array.isArray(parsed);
    } catch (error) {
        // If parsing fails, it's not valid JSON, hence not an array
        return false;
    }
}