How to calculate the sum of all values per month of a JSON file in React and display it as a single-CodePudding

First of all I would like to apologize in case I didn't elaborate my question in the best way, as a junior developer there are times when I find it hard to ask the right question, I guess we all have been there right?

The information in my JSON file looks like this:

[
{
"id": 1,
"Period From": "2021-01-31T23:00:00.00.000Z",
"Period To": "2021-02-27T23:00:00:00.000Z",
"Transaction Date": null,
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": "2
"Revenue": "0.001091
},
{
"id": 2,
"Period From": "2021-01-31T23:00:00.000Z",
"Period To": "2021-02-27T23:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": 4,
"Revenue": 0.002181
},
{
"id": 20,
"Period From": "2021-03-31T22:00:00.000Z",
"Period To": "2021-04-29T22:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": 1,
"Revenue": 0.004827
},
{
"id": 2000,
"Period From": "2021-04-29T22:00:00.000Z",
"Period To": "2021-05-26T22:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": 4,
"Revenue": 0.002181
},
{
"id": 20,
"Period From": "2021-03-31T22:00:00.000Z",
"Period To": "2021-04-29T22:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": 4,
"Revenue": 0.003288
},
{
"id": 2001,
"Period From": "2021-04-29T22:00:00.000Z",
"Period To": "2021-05-26T22:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Territory": "US",
"Quantity": 1,
"Revenue": 0.000822
},
{
"id": 5010,
"Period From": "2021-04-30T22:00:00.000Z",
"Period To": "2021-05-30T22:00:00.000Z",
"Distributor": "DistributeName",
"UPC": "UpcNumber",
"cat. no.": "CatNumber",
"ISRC": "IsrcNumber",
"Label": "LabelName",
"Release Title": "ReleaseTitle",
"Track Title": "TrackTitle",
"Mix Name": "Original Mix",
"Artist": "ArtistName",
"Quantity": 150,
"Revenue": 0.007441
}
]

The above info comes from my table in my database, which is an array of multiple single objects with different ids, I have only published 3 examples but I have more than 100k objects in the same table.

I have a component called LineChart.jsx

import React from "react";
import  { useState, useEffect } from 'react'

import {
  Chart as ChartJS,
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Tooltip,
  Legend,
  Filler,
} from 'chart.js';

import { Line } from 'react-chartjs-2';

ChartJS.register(
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Tooltip,
  Legend,
  Filler
);


const LineChart = ({ songs }) => {

  let data = {
    labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
    //  labels: songs?.map((song) => song["Period From"]), // this was my original idea, it works but it shows all the revenues of each individual track on the chart

    datasets: [{
      label: 'Revenue',
      data: [65, 59, 80, 81, 56, 55, 40, 30, 40, 80, 25, 50],
      // data: songs?.map((song) => song.Revenue), // same as above this was my original idea, it works but it shows all the revenues of each individual track on the chart
      fill: true,
      backgroundColor: [
        'rgba(52, 52, 52, 0.1)',
      ],
      borderColor: [
        "#000",
      ],
      borderWidth: 2,
      tension: 0.1, 
    }]
  };

  let options = {
    maintainAspectRatio: false,
    scales: {
    },
    legend: {
      labels: {
        fontSize: 25,
      },
    },
  }

  return (
    <div>
      <div>
      <Line className='p-10'
        data={data}
        height={400}
        options={options}

      />
    </div>
    </div>
    )
}



export default LineChart

At the moment it looks like this, which looks like an electrocardiogram. ( I'm not sure if I can make jokes on the platform.) current chart look

But what I want to achieve is to make it look like this:

desired chart look

songs is a props that I am passing from another component to call the data from my table in the database.

I need to calculate the values for each month and make the final result for one month, for example: Period From 01-01-2023 and Period To 31-01-2023="MM/yyyy" or something like that.

I hope you guys will be able to help me and if there is something I have not expressed well please leave me a comment before giving a negative vote. :))

UPDATE

Based on @Badacadabra's answer I have done this, but I can only see the revenue in May. I just changed the for loop a bit to make it more understandable for me.

const LineChart = ({ songs }) => {

let monthlyTotals = new Array(12).fill(0);

for (let i = 0; i < songs.length; i  ) {
  let obj = songs[i];
  let fromMonth = new Date(obj['Period From']).getMonth();
  let toMonth = new Date(obj['Period To']).getMonth();

  if (fromMonth === toMonth) {
    monthlyTotals[fromMonth]  = obj.Quantity * obj.Revenue;
  }
}


  let data = {
    labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],

    datasets: [{
      label: 'Revenue',
      data: monthlyTotals,
      fill: true,
      backgroundColor: [
        'rgba(52, 52, 52, 0.1)',
      ],
      borderColor: [
        "#000",
      ],
      borderWidth: 2,
      tension: 0.1, 
    }]
  };

  let options = {
    maintainAspectRatio: false,
    scales: {
    },
    legend: {
      labels: {
        fontSize: 25,
      },
    },
  }

  return (
    <div>
      <div>
      <Line
        data={data}
        height={400}
        options={options}
      />
    </div>
    </div>
    )
}

New Result

CodePudding user response：

If I understand your question correctly, you are looking for a strategy to preprocess your data. Here is a simple implementation:

// Declare and initialize an array containing 12 values (because there are 12 months)
let monthlyTotals = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];

// Loop through the data array
for (let obj of data) {
  // Parse date strings and get month number (0 is January and 11 is December)
  let fromMonth = new Date(obj['Period From']).getMonth(),
      toMonth = new Date(obj['Period To']).getMonth();
   
  // Make sure that your period is correct to avoid an inconsistent result   
  if (fromMonth === toMonth) {
    // Use the month number as an index to update the right value in monthlyTotals
    monthlyTotals[fromMonth]  = obj.Quantity * obj.Revenue;
  }
}

As you can see, I use this formula to update monthly totals: obj.Quantity * obj.Revenue. If obj.Quantity is not relevant in your case, for instance if obj.Revenue already takes into account the quantity, you can remove it easily and just keep what you need.

I created sample data for the first three months to give you a full example:

const ctx = document.getElementById('myChart');

const data = [
  {
    "id": 1,
    "Period From": "2022-12-31T23:00:00.000Z",
    "Period To": "2023-01-30T23:00:00.000Z",
    "Quantity": 2,
    "Revenue": 3
  },
  {
    "id": 2,
    "Period From": "2022-12-31T23:00:00.000Z",
    "Period To": "2023-01-30T23:00:00.000Z",
    "Quantity": 4,
    "Revenue": 7
  },
  {
    "id": 3,
    "Period From": "2022-12-31T23:00:00.000Z",
    "Period To": "2023-01-30T23:00:00.000Z",
    "Quantity": 1,
    "Revenue": 2
  },
  {
    "id": 4,
    "Period From": "2023-01-31T23:00:00.000Z",
    "Period To": "2023-02-27T23:00:00.000Z",
    "Quantity": 5,
    "Revenue": 5
  },
  {
    "id": 5,
    "Period From": "2023-01-31T23:00:00.000Z",
    "Period To": "2023-02-27T23:00:00.000Z",
    "Quantity": 3,
    "Revenue": 4
  },
  {
    "id": 6,
    "Period From": "2023-02-28T23:00:00.000Z",
    "Period To": "2023-03-30T23:00:00.000Z",
    "Quantity": 2,
    "Revenue": 10
  }
];

let monthlyTotals = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
    
for (let obj of data) {
  let fromMonth = new Date(obj['Period From']).getMonth(),
      toMonth = new Date(obj['Period To']).getMonth();
      
   if (fromMonth === toMonth) {
     monthlyTotals[fromMonth]  = obj.Quantity * obj.Revenue;
   }
}

new Chart(ctx, {
  type: 'line',
  data: {
    labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
    datasets: [{
      label: 'Revenue',
      data: monthlyTotals,
      borderWidth: 1,
      fill: true
    }]
  },
  options: {
    scales: {
      y: {
        beginAtZero: true
      }
    }
  }
});

.chart-container {
  position: relative;
  height: 90vh;
}

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<div >
  <canvas id="myChart"></canvas>
</div>

Given that you dataset is big, you may face performance issues. If this occurs, you should preprocess from the back-end.