CODEWITHSUNDEEP
google-sheets
dtm
dtm

Reputation: 1

My Google Sheets Query is returning only one result instead of one for each month

I'm running reports on sales from data across four sheets, to return the monthly total for each type of sale, based on the contents of a reference cell.

For some sale types, it's working fine and returning me the total for each month, but for others it's just returning a singular total.

In some cases sales only happen in a single month, but in the example below those happen in the second month of the sales period (Aug) but are returning in the first column.

I'd rather that was blank when no sales happen (or a 0 value), but I can't get it to run past the final month in which there were sales, or to display in the correct column.

Very much first times working with these kinds of queries, so help very much appreciated.

Col30: year
col29: month
col31: mm/yyyy
Col10: status (1 or -1)
Col13: amount
col21: sale type

=
QUERY(
QUERY({JUL_SEP_22!$A$1:$AF;OCT_DEC_22!$A$1:$AF;JAN_MAR_23!$A$1:$AF;APR_JUN_23!$A:$AF}, 
"
SELECT Col30, Col29, Col31, SUM(Col13)

WHERE 
Col10 = 1 
and Col13 >0 
and Col21 = '"&B3&"'

GROUP BY Col29, Col30, Col31

ORDER BY Col30, Col29

LABEL SUM(Col13) '"&B3&"'
"),

"SELECT Col4")

Upvotes: 0

Views: 146

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 29982

Here's one approach added to your sheet:

=let(Σ,vstack(JUL_SEP_22!A2:U,OCT_DEC_22!A2:U,JAN_MAR_23!A2:U,APR_JUN_23!A2:U),
       transpose(query(hstack(Σ,arrayformula(eomonth(index(Σ,,2),))),"Select Col22, sum(Col13) Where Col10=1 and Col13>0 group by Col22 pivot Col21 format Col22 'mmm-YYY'")))
  • The helper columns are not taken into account for this formula. date is taken directly from Column_B

enter image description here

Upvotes: 0

Related Questions