I am calculating a TOTAL's row for an FTR table that i want to stack below the FTR table like this:
=ArrayFormula(
{
FTR Table ;
TOTAL
}
The FTR Table is also calculated via Google Query. The table contains some cells having 0%. For a particular Year, say 2018 (Cell C1), the FTR Table may look like below:
+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| FTR % | 2018-Jan | 2018-Feb | 2018-Mar | 2018-Apr | 2018-May | 2018-Jun | 2018-Jul | 2018-Aug | 2018-Sep | 2018-Oct | 2018-Nov | 2018-Dec |
+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Allana | | | | | | | | 0.00% | | 76.92% | 85.00% | 83.72% |
| Mark | | | | 0.00% | | | 0.00% | 75.00% | 86.21% | 76.32% | 90.16% | 91.43% |
| Jane | | 57.50% | 68.97% | 89.47% | 81.82% | 81.36% | 91.11% | 90.24% | 85.71% | 88.89% | 82.69% | 89.61% |
| Santorin | | | | 0.00% | | | | | | | | |
| Lamaiye | | | | | | | | | | | | 85.71% |
| Suez | 80.00% | | 86.67% | 75.00% | 81.08% | 87.27% | 91.80% | 79.69% | 81.43% | 81.40% | 71.70% | 76.00% |
+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
and the TOTAL row may show as AVERAGE % of each Year-Month Column :
+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| TOTAL | 80.00% | 57.50% | 77.82% | 82.24% | 81.45% | 84.31% | 91.46% | 81.64% | 84.45% | 80.88% | 82.39% | 85.29% |
+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
Note: See 2018-July & 2018-Aug where there are 0%'s but the AVERAGE is calculated correctly.
2018-July = 91.46%
2018-Aug = 81.64%
But for a particular Year 2017, the table may just show 0% and blank Column Headers also, to maintain Column structure of the stacked tables (one above the other).
+-------+--+--+--+----------+----------+
| FTR % | | | | 2017-Aug | 2017-Dec |
+-------+--+--+--+----------+----------+
| Mark | | | | | 0.00% |
| Jane | | | | 0.00% | |
+-------+--+--+--+----------+----------+
However, the problem arises here for the TOTAL row as it results in
+---------+
| #VALUE! |
+---------+
with error description: Error:Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC
This single cell value for the TOTAL row breaks the Tables Column structure and the above stacked Tables e.g. the FTR Table also does not show up. I need to have some way to show the TOTAL row Column cells as BLANKS or 0%'s (like as shown below) whenever there is such kind of error:
+-------+-------+-------+-------+-------+-------+
| TOTAL | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
+-------+-------+-------+-------+-------+-------+
I cannot share any Spreadsheet due to legal restrictions on my laptop, so I am pasting the Google Query for the TOTAL's row only as it is similar to the FTR table, except for a few changes:
=ARRAYFORMULA(TRANSPOSE(QUERY(QUERY(QUERY({QUERY(
{TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")&
IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))},"select Col1,Col8,Col15,Col22,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' group by Col1,Col8,Col15,Col22 order by Col8"),
IFNA(VLOOKUP(QUERY({TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")&IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))},
"select Col1,Col8,Col15,Col22,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' group by Col1,Col8,Col15,Col22 order by Col8")
,QUERY({TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")&
IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))},
"select Col1,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' and Col16!= 'NO - all fine' AND Col16!='-' group by Col8,Col1")
,2,0))
}
,"SELECT Col1,Col2,Col3,Col4,(1-Col6/Col5 * 1) WHERE (1-Col6/Col5 * 1)>0 label Col1 'A', Col2 'B', Col3 'C', Col4 'D', (1-Col6/Col5 * 1) 'diff'"),
"SELECT Col4, AVG(Col5) GROUP BY Col4 LABEL AVG(Col5) 'TOTAL' FORMAT AVG(Col5) '0.00%'"),"SELECT Col2")))
Credits: Thanks to @player0 for showing me this method of Stacking Tables whilst maintaining the Column structure.
EDIT:
I tried the below solution with an IFERROR() function (also shown earlier by @Player0, which adds a blank row between two tables), for generating the TOTAL row if no data or on error:
=TRANSPOSE(
QUERY(
TRANSPOSE(
IFERROR(
ARRAYFORMULA(TRANSPOSE(QUERY(QUERY(QUERY({QUERY( {TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")& IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))},"select Col1,Col8,Col15,Col22,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' group by Col1,Col8,Col15,Col22 order by Col8"), IFNA(VLOOKUP(QUERY({TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")&IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))}, "select Col1,Col8,Col15,Col22,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' group by Col1,Col8,Col15,Col22 order by Col8") ,QUERY({TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")& IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))}, "select Col1,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' and Col16!= 'NO - all fine' AND Col16!='-' group by Col8,Col1") ,2,0)) } ,"SELECT Col1,Col2,Col3,Col4,(1-Col6/Col5 * 1) WHERE (1-Col6/Col5 * 1)>0 label Col1 'A', Col2 'B', Col3 'C', Col4 'D', (1-Col6/Col5 * 1) 'diff'"), "SELECT Col4, AVG(Col5) GROUP BY Col4 LABEL AVG(Col5) 'TOTAL' FORMAT AVG(Col5) '0.00%'"),"SELECT Col2 FORMAT Col2 '0.00%'"))),
ARRAYFORMULA(SPLIT(REPT("TOTAL ?", COLUMNS(TRANSPOSE(QUERY({TEXT('Study Report'!G1:G,"yyyy-mmm;;")&"?"&'Study Report'!N1:N, 'Study Report'!B1:F, TEXT('Study Report'!G1:G,"yyyy;;"), TEXT('Study Report'!G1:G,"yyyy-mm;;"), 'Study Report'!H1:T, TEXT('Study Report'!G1:G,"yyyy-mm;;")& IF('Study Report'!G1:G="",,"?"&TEXT('Study Report'!G1:G, "yyyy-mmm;;"))},"select Col22,count(Col2) where Col8 is not null and Col7 = '"&C1&"' and Col13 != 'Cancelled' group by Col22")))), "?") ))),"SELECT Col1 FORMAT Col1 '0.00%'"))
The Only issue is that all the Values will show the word TOTAL for all cells, instead of only 1st cell and rest cells as 0.00%:
+--------+--------+--------+--------+--------+--------+
| TOTAL | TOTAL | TOTAL | TOTAL | TOTAL | TOTAL |
+--------+--------+--------+--------+--------+--------+
Any better idea instead to show it like below:
+-------+-------+-------+-------+-------+-------+
| TOTAL | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
+-------+-------+-------+-------+-------+-------+
question from:
https://stackoverflow.com/questions/65843243/google-query-show-total-row-as-blank-or-0-cells-if-query-table-returns-empty 
