How to show the data from another spreadsheet but only the latest week

Question

I have 2 spreadsheet, the first one consists of all of the data of withdrawal and deposit of items. The second spreadsheet is where the summary of the data shown.

Here is the master data link: https://docs.google.com/spreadsheets/d/1DjSXpLWqFagAI2l2vth4C-XDhV77sSHfYtCEsoG5H_M/edit?usp=sharing

And here is the summary data link: https://docs.google.com/spreadsheets/d/1y8pnYnTpRktyr2BxKDCLPVIqm0pBHkO7AiVfkyhpEC8/edit?usp=sharing

I want to show the data from the master data to the summary data, below the "list", like the data of when, who, and which item is deposited or withdrew, but only the latest week. My knowledge of the query function is not that advanced and i really don't know how to so can you please enlighten me the formula to do so? Thank you

Answer 1

To just list the rows where the date in column A is within the last seven days, use query() like this:

=query( 
  importrange("1DjSXpLWqFagAI2l2vth4C-XDhV77sSHfYtCEsoG5H_M)", "Data Input DP/WD!A1:F"), 
  "where toDate(Col1) >= date '" & text(today() - 7, "yyyy-mm-dd") & "' ", 
  1 
)

See the Solution2 sheet in your sample spreadsheet.

To aggregate the data, use a group by clause. If need be, you can coerce numeric values to pure numbers and text strings that look like numbers to numbers with an iferror(value()) pattern in the query(), like this:

=arrayformula( 
  query( 
    iferror( 
      value(importrange("1DjSXpLWqFagAI2l2vth4C-XDhV77sSHfYtCEsoG5H_M)", "Data Input DP/WD!A1:F")), 
      importrange("1DjSXpLWqFagAI2l2vth4C-XDhV77sSHfYtCEsoG5H_M)", "Data Input DP/WD!A1:F") 
    ), 
    "select sum(Col3), sum(Col4), sum(Col5), sum(Col6) 
     where Col1 >= " & today() - 7, 
    1 
  ) 
)

See the Solution1 sheet in your sample spreadsheet.