Search code examples
google-sheetspivotgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Query with date Pivot and date order

I have this simple table and query:

https://docs.google.com/spreadsheets/d/1MwdP08WWmkG13fGmKjB5hpL-D_uqgAu0lsXrO8WcE1M

the problem is, I cannot get the columns to be ordered by date. I've tried transpose+sort+transpose, but it doesn't work as it puts "2019-10-1" before "2019-9-1" (because it interprets date as text). Is there any way to get this sorted?

Solution

  • you can do:

    =ARRAYFORMULA(QUERY(QUERY(A:D; 
 "select B,sum(C),sum(D) 
  where B is not null 
  group by B 
  pivot A"); 
 "select Col1,"&TEXTJOIN(","; 1; 
  {"Col"&ROW(INDIRECT("A2:A"&COUNTUNIQUE(A2:A)+1))\ 
   "Col"&ROW(INDIRECT("A"&2+COUNTUNIQUE(A2:A)&":A"&COUNTUNIQUE(A2:A)*2+1))})))

    0

    and if you want to remove that sum:

    =ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY(QUERY(A:D; 
 "select B,sum(C),sum(D) 
  where B is not null 
  group by B 
  pivot A "); 
 "select Col1,"&TEXTJOIN(","; 1; 
  {"Col"&ROW(INDIRECT("A2:A"&COUNTUNIQUE(A2:A)+1))\ 
   "Col"&ROW(INDIRECT("A"&2+COUNTUNIQUE(A2:A)&":A"&COUNTUNIQUE(A2:A)*2+1))})));
  " sum "; " "))

    0

    where can I add a date filter where A >= date '2019-07-01'. I've tried adding it in the first block, but I keep getting the error: "Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: ColXX"

    =ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY(QUERY(A:D; 
 "select B,sum(C),sum(D) 
  where B is not null 
    and A >= date '2019-7-1'
  group by B 
  pivot A "); 
 "select Col1,"&TEXTJOIN(","; 1; 
  {"Col"&ROW(INDIRECT("A2:A"&COUNTUNIQUEIFS(A2:A; A2:A; ">="&DATE(2019;7;1))+1))\ 
   "Col"&ROW(INDIRECT("A"&2 +COUNTUNIQUEIFS(A2:A; A2:A; ">="&DATE(2019;7;1))&":A"&
                             COUNTUNIQUEIFS(A2:A; A2:A; ">="&DATE(2019;7;1))*2+1))})));
  " sum "; " "))

    0