Using Query to replace PIVOT

Question

Since Query is such a powerful formula, i try to avoid my team members to avoid messing up the pivot. I try to to come out with query to replace my pivot. Based on the raw data Google Sheet Trix, i try to sum column based on the criteria i have described in Grey colour (B2:E2). Appreciate if someone could lay some foundation for me, so i can further tweaks based on the formula you create here.

Answer 1

I created a new tab and entered the following in cell A1:

=query(
  {'RAW DATA'!B:B,'RAW DATA'!E:E,'RAW DATA'!G:J},
  "select Col1, sum(Col3), sum(Col4), sum(Col5), sum(Col6) 
   where Col1 is not null and Col2<>'Black and Yellow' 
   group by Col1"
)

Answer 2

Use a query() with a group by clause, like this:

=query( 
  'RAW DATA'!B1:J, 
  "select B, sum(G), sum(H), sum(I), sum(J) 
   where B is not null and E <> 'Black' and E <> 'Yellow' 
   group by B", 
  1 
)

The result table will only include weeks where there is at least one data row where E is not Black or Yellow.

See the new Solution sheet in your sample spreadsheet.

Answer 3

Here is your solution. which also included in new tab named Solution Pivot in Query.

=ArrayFormula(QUERY(SPLIT(FLATTEN(TO_TEXT('RAW DATA'!B2:B)&"|"&'RAW DATA'!E2:E&"|"&'RAW DATA'!G1:J1&"|"&'RAW DATA'!G2:J),"|"),"Select Col1, Sum(Col4) where Col2 is not null And Col2 <> 'Yellow' And Col2 <> 'Black' Group by Col1 Pivot Col3 label Col1 'Date'"))