Combine data from multiple sheets to create a stacked line chart

Question

I have multiple sheets with three columns each: Date, Invested Amt, and Return Amt. I am trying to merge the values and get a Result dataset to create a single stacked line chart of Invested vs Return against Date.

Update: Demo sheet is available on, https://docs.google.com/spreadsheets/d/1mEQXW8202UsYkVzRVvZEbFrjzioDTB7KFDKQNy4zgEo/edit?usp=sharing

So my questions are,

1. How to combine these values from multiple sheets to create a stacked line chart while taking care of the missing data?
2. How to keep the final chart updated automatically when new rows are added the sheets?

Answer 1

Use an { array expression } to stack the data, and a query() to aggregate it by date, like this:

=arrayformula( query( 
  { 
    { text(Sheet1!A1:A, "yyyy-mm"); text(Sheet2!A2:A, "yyyy-mm"); text(Sheet3!A2:A, "yyyy-mm") }, 
    { Sheet1!B1:C; Sheet2!B2:C; Sheet3!B2:C } 
  }, 
  "select Col1, sum(Col2), sum(Col3) 
   where Col2 is not null 
   group by Col1 
   label Col1 'Month' ", 
  1 
) )

The arrayformula() is required because of the conversion of dates to months with text().