Creating an object with FY as 'key' and Quarter as 'value'

I'm trying to create an object whose 'key' will be the fiscal year (FY) and 'values' are the quarter dates.

//Format
{
   FY:{
        [Quarter dates]
   },
}

The quarter dates count varies from 1 to 4. If only one-quarter info is given, take the previous FY as a reference and increase by 1 to know the present FY.

Based on the Period end dates I'm able to group the quarters but calculating the fiscal year is what I need help with. The FY will follow these rules.

The most repeated quarter years will be the fiscal year.
If quarters have two different years with a count of 2 each then we can pick the smallest year as FY. Eg: Periods =[06/21], Quarters =[09/20,12/20,03/21,06/21] then FY =2020

A company can change its period end date so you can expect a gap in quarter dates. In the following example, the company used Dec as a period-end till 2019 after that they are using Mar as the period-end. A gap of one quarter (Jan, Feb, Mar months) has been omitted.

const quarters = [
    "12 / 30 / 2021",
    "09 / 30 / 2021",
    "06 / 30 / 2021",
    "03 / 30 / 2021",
    "12 / 30 / 2020",
    "09 / 30 / 2020",
    "06 / 30 / 2020",
    "12 / 30 / 2019",
    "09 / 30 / 2019",
    "06 / 30 / 2019",
    "03 / 30 / 2019",
    "12 / 30 / 2018",
    "09 / 30 / 2018",
    "06 / 30 / 2018",
    "03 / 30 / 2018",
];
const periods = [
    "03 / 30 / 2021",
    "12 / 30 / 2019",
    "12 / 30 / 2018",
    "12/30/2017"
];

const groupQuarters = [];
let group = [];
while (periods.length > 0) {
    let p = new Date(periods[0].toString());
    group = [];
    while (quarters.length > 0) {
        let q = new Date(quarters[0].toString());
        if (q > p) {
            group.push(quarters[0]);
            quarters.shift();
        } else {
            break;
        }
    }
    const yearCal = (groups) => {
        // unable to figure this out.
        return 2020;
    }
    groupQuarters.push({ [yearCal(group)]: group });
    periods.shift();
}


console.log(groupQuarters);

Expected output:
[
  {
      2021: [
          '12 / 30 / 2021',
          '09 / 30 / 2021',
          '06 / 30 / 2021'
      ]
  },
  {
      2020: [
          '03 / 30 / 2021',
          '12 / 30 / 2020',
          '09 / 30 / 2020',
          '06 / 30 / 2020'
      ]
  },
  {
      2019: [
          '12 / 30 / 2019',
          '09 / 30 / 2019',
          '06 / 30 / 2019',
          '03 / 30 / 2019'
      ]
  },
  {
      2018: [
          '12 / 30 / 2018',
          '09 / 30 / 2018',
          '06 / 30 / 2018',
          '03 / 30 / 2018'
      ]
  }
]

Test Case - 2:
const quarters = [
  "12 / 30 / 2020",
  "09 / 30 / 2020",
  "06 / 30 / 2020",
  "03 / 30 / 2020",
  "12 / 30 / 2019",
  "09 / 30 / 2019",
  "06 / 30 / 2019",
  "03 / 30 / 2019",
  "12 / 30 / 2018"
];
const periods = [
  "09 / 30 / 2020",
  "09 / 30 / 2019",
  "09 / 30 / 2018",
];

Expected Output:
[
  {
    2021: [
      '12 / 30 / 2020',
    ]
  },
  {
    2020: [
      "09 / 30 / 2020",
      "06 / 30 / 2020",
      "03 / 30 / 2020",
      "12 / 30 / 2019",
    ]
  },
  {
    2019: [
      "09 / 30 / 2019",
      "06 / 30 / 2019",
      "03 / 30 / 2019",
      "12 / 30 / 2018",
    ]
  },
]

CodePudding user response：

Assuming there are no gaps within a period -- i.e. no gaps between two quarters that belong to the same period -- you could apply this logic:

When the last quarter is known of the period (i.e. it equals the period-end), subtract 2 quarters and take that quarter's year as financial year
When the last quarter is not known of the period -- this may happen at the start of the array, where the current period is not yet completed -- add one quarter to the first quarter of that period and take that quarter's year as financial year.

As it is nice to map the periods array one-to-one to get the result array, it might sometimes be needed to strip the last period entry (like in the test case 1), or insert an unfinished period at the start of that array (also in test case 1).

Not related, but I avoided Date objects for this task, and included functions to convert the input strings to numbers and back:

function chunkByYear(quarters, periods) {
    // Converter functions between string and unique quarter number
    const toQuarter = s => s.match(/^\d |\d $/g).reduce((m, y) => m/3 - 1   y*4);
    const toDate = q => ((q % 4   1)*3   "/30/"   (q >> 2)).padStart(10, "0");

    const periodNums = periods.map(toQuarter);
    const quarterNums = quarters.map(toQuarter);
    // Make sure all quarters are after at least one period end
    while (quarterNums[quarterNums.length-1] <= periodNums[periodNums.length-1]) periodNums.push(periodNums[periodNums.length-1] - 4);
    // Remove periods that have no content:
    while (quarterNums[0] <= periodNums[0]) periodNums.shift();
    quarterNums.push(0); // To make sure `findIndex` always finds an index
    return periodNums.map((p, i, {length}) => {
        const period = quarterNums.splice(0, quarterNums.findIndex(q => q <= p));
        return { [(i ? period[0] - 2 : period[period.length-1]   1) >> 2]: period.map(toDate) };
    });
}

{ // test 1
    const quarters = [
        "12 / 30 / 2021", "09 / 30 / 2021", "06 / 30 / 2021",
        "03 / 30 / 2021", "12 / 30 / 2020", "09 / 30 / 2020", "06 / 30 / 2020",
        "12 / 30 / 2019", "09 / 30 / 2019", "06 / 30 / 2019", "03 / 30 / 2019",
        "12 / 30 / 2018", "09 / 30 / 2018", "06 / 30 / 2018", "03 / 30 / 2018",
    ];
    const periods =  ["03 / 30 / 2021", "12 / 30 / 2019", "12 / 30 / 2018", "12 / 30 / 2017"];
    console.log(chunkByYear(quarters, periods));
}
{ // test 2
    const quarters = [
        "12 / 30 / 2020",
        "09 / 30 / 2020", "06 / 30 / 2020", "03 / 30 / 2020", "12 / 30 / 2019",
        "09 / 30 / 2019", "06 / 30 / 2019", "03 / 30 / 2019", "12 / 30 / 2018"
    ];
    const periods =  ["09 / 30 / 2020", "09 / 30 / 2019", "09 / 30 / 2018"];
    console.log(chunkByYear(quarters, periods));
}
{ // test 3
    const quarters = [
        "06 / 30 / 2021", "03 / 30 / 2021", "12 / 30 / 2020", "09 / 30 / 2020",
        "06 / 30 / 2020", "03 / 30 / 2020", "12 / 30 / 2019", "09 / 30 / 2019",
        "06 / 30 / 2019",
    ];
    const periods = ["06 / 30 / 2021", "06 / 30 / 2020", "06 / 30 / 2019"];
    console.log(chunkByYear(quarters, periods));
}
{ // test 4
    const quarters = [
        "06 / 30 / 2021", "03 / 30 / 2021", "12 / 30 / 2020", "09 / 30 / 2020",
        "06 / 31 / 2020", "03 / 30 / 2020", "12 / 30 / 2019", "09 / 30 / 2019",
        "06 / 30 / 2019", "03 / 30 / 2019", "12 / 30 / 2018", "09 / 30 / 2018",
    ];
    const periods = ["06 / 30 / 2021", "06 / 31 / 2020", "06 / 30 / 2019"];
    console.log(chunkByYear(quarters, periods));
}

CodePudding user response：

You can try it :

const quarters = [
    "12 / 30 / 2021",
    "09 / 30 / 2021",
    "06 / 30 / 2021",
    "03 / 30 / 2021",
    "12 / 30 / 2020",
    "09 / 30 / 2020",
    "06 / 30 / 2020",
    "12 / 30 / 2019",
    "09 / 30 / 2019",
    "06 / 30 / 2019",
    "03 / 30 / 2019",
    "12 / 30 / 2018",
    "09 / 30 / 2018",
    "06 / 30 / 2018",
    "03 / 30 / 2018",
]
const periods = [
    "03 / 30 / 2021",
    "12 / 30 / 2019",
    "12 / 30 / 2018",
    "12/30/2017"
]
const quarters1 = [
  "12 / 30 / 2020",
  "09 / 30 / 2020",
  "06 / 30 / 2020",
  "03 / 30 / 2020",
  "12 / 30 / 2019",
  "09 / 30 / 2019",
  "06 / 30 / 2019",
  "03 / 30 / 2019",
  "12 / 30 / 2018"
];
const periods1 = [
  "09 / 30 / 2020",
  "09 / 30 / 2019",
  "09 / 30 / 2018",
];
const getGroupQuarter = (qt, pr) => {
  let tmpCheck = qt
  return pr.reduce((result,period) => {
    let datePeriod = new Date(period)
    let key = getKeyQuarter(datePeriod)
    let qtrFill =  tmpCheck.filter(quarter => new Date(quarter) > datePeriod)
    tmpCheck =  tmpCheck.filter(quarter =>!(new Date(quarter) > datePeriod))
    return !qtrFill.length ? result :[...result, {[key] : qtrFill}]
  }, [])
}
const getKeyQuarter = (pr) => {
  let monthValidate = pr.getMonth()
  return monthValidate > 5 ? pr.getFullYear()   1 : pr.getFullYear()
}
console.log(getGroupQuarter(quarters1, periods1));
console.log(getGroupQuarter(quarters, periods));