0

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

1
  • It is unclear what you mean by "the latest week". Please show your hand-entered expected results in the sample spreadsheet. Aug 4, 2021 at 17:40

1 Answer 1

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.

2
  • I have a question from your answer: which cell should i choose to refer to the value? and i've updated the result i wanted to see in the summary data spreadsheet
    – arviona
    Aug 5, 2021 at 13:59
  • Edited the answer add another formula to match your new expected results, and to explain what iferror(value()) is used for. Aug 5, 2021 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.