Handling blanks and dealing with sums in pivots with google sheets query

Question

I'm trying to cope with blanks and a total row in google sheets. This is my ridiculous solution. I'm sure, someone has a much better solution

={query({query (query(vd!A:G,"select C,sum(G) where F = 'fm' or F = 'fma' group by C pivot F order by C"),"select Col1"),arrayformula(query (N(query(vd!A:G,"select C,sum(G) where F = 'fm' or F = 'fma' group by C pivot F order by C")),"select Col2, Col3"))},"select Col1,Col2,Col3, Col3-Col2 where Col2-Col3 > 50000 or Col2-Col3 < -50000 label Col1 'Program', Col2 'Forecast', Col3 'Actuals', Col3-Col2 'Variance'");{"Total",index(query({query (query(vd!A:G,"select C,sum(G) where F = 'fm' or F = 'fma' group by C pivot F order by C"),"select Col1"),arrayformula(query (N(query(vd!A:G,"select C,sum(G) where F = 'fm' or F = 'fma' group by C pivot F order by C")),"select Col2, Col3"))},"select sum(Col2),sum(Col3), sum(Col3)-sum(Col2) where Col2-Col3 > 50000 or Col2-Col3 < -50000"),2)}}

I made a view of the sheet this is on for easier handling. Is there an easy way to anonoymize data?

https://docs.google.com/spreadsheets/d/1YA7xgOtOXKhtOos6acaS40BMxBcAE8g9VMkdUaBGrMg/edit?usp=sharing

No way to anoyingmouse your data, what you can do to avoid sharing data you don't want to share is: Make a copy of the sheet with some sample dummy data, after all what the community needs in order to answer is generic or sample data, and the minimal of it. — Aerials

player0 player0 · Accepted Answer · 2020-07-17T20:00:50

try:

=ARRAYFORMULA({"Program", "Forecast", "Actuals", "Variance"; 
 QUERY(QUERY({INDEX(QUERY(vd!A:G,
 "select C,sum(G) 
  where F matches 'fma|fm'
  group by C 
  pivot F 
  order by C"),, 1), 
 QUERY(N(QUERY(vd!A:G, 
 "select C,sum(G) 
  where F matches 'fma|fm'
  group by C 
  pivot F 
  order by C")), 
 "select Col2,Col3")},
 "select Col1,Col2,Col3,Col3-Col2 
  where Col2-Col3 >  50000 
     or Col2-Col3 < -50000", 0), 
 "offset 1", 0); 
 "Total", QUERY(QUERY(QUERY(QUERY({INDEX(QUERY(vd!A:G,
 "select C,sum(G) 
  where F matches 'fma|fm'
  group by C 
  pivot F 
  order by C"),, 1), 
 QUERY(N(QUERY(vd!A:G, 
 "select C,sum(G) 
  where F matches 'fma|fm'
  group by C 
  pivot F 
  order by C")), 
 "select Col2,Col3")},
 "select Col2,Col3,Col3-Col2 
  where Col2-Col3 >  50000 
     or Col2-Col3 < -50000", 0), 
 "offset 1", 0), 
 "select sum(Col1),sum(Col2),sum(Col3)"), 
 "offset 1", 0)})

Handling blanks and dealing with sums in pivots with google sheets query

1 Answers