Home > Mobile >  Google Sheets - Query Pivot - show all results
Google Sheets - Query Pivot - show all results

Time:05-06

In google sheets have a pivot table with columns with text day ranges 1-30, 31-60, 61-90, 90-120, >120 where some records fall under those day ranges.

This is sample data:

Unique Account Doc Amount Day Range
1 123456 1000 1-30
2 561530 2000 >120
3 123456 1500 61-90
4 25106 3000 1-30

I can get this data to pivot using standard pivot tables but users needs it to be clean without the pivot table buttons and formatting. I am trying to convert to google query function but I am stumped. I'd could try the option of pivoting and then calling the pivot to a query to remove the formatting but that seems redundant and there is a lot of other things happening in my sheet so afraid of making updating slow.

End result would look like below where the day ranges are pivoted and amount is showing for each record.

All columns need to be preserved as if there are null values all results appear even null/zero values for Amount as with column 31-60 and 91-120 showing the columns with no results. I use a unique id to ensure that all records come back as some of the

I can get the query to pivot with:

=query(rawdata,"Select Z,B,E,D,F,H,J, Sum(N) where B="&$C$31&" Group by Z,B,E,D,F,H,J pivot AA order by F",1)

However if the filter on B only has some day ranges and not others it will only show those columns with data.

Link to google sheet with sample data:

enter image description here

  • Related