1

I have a report I am developing that needs to be in google sheets Sheet 1 contains all the current cases that are opened and owned by a case owner, 1 row per case, multiple cases per case owner per month (sample sheet has 4 months of data) Sheet 2 contains all the cases that were closed and who closed them, 1 row per case, multiple cases per case owner per month (sample sheet has 4 months of data)

There could be case owners in the open sheet that don't exist in the closed sheet There could be case owners in the closed sheet that don't exist in the open sheet

I know how create a query() function that summarises both raw data separately - what I want to be able to do is query a joined table to get open and closed cases in 1 table. In the sample file there are 2 tables in A5 and F5. These were me looking at how the query() function worked - the actual report I want is in K5

Where I am struggling is bringing this together into one summary report which shows the last 3 months of data

To clarify - what I want to do is have one function that queries from the 2 RAW sheets and creates the summary report

The sample sheet should more clearly explain what I need to do

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

Output for the OPEN cases:

=query('Raw Data'!A:N,"Select B, count(A) where N >= date '2020-02-01' group by B pivot N")

Output for the CLOSED cases

=query('Raw Data - Closed'!A:N,"Select A, count(B) where H >= date '2020-02-01' AND H <= date '2020-04-01' group by A pivot H")

Any pointers?

Gazing South
  • 115
  • 8

1 Answers1

0

try:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE({{
 IF(ISNUMBER(A5:D5*1), A5:D5&" A"&A3, A5:D5); A6:D}, 
 IFNA(VLOOKUP(A5:A, {IF(ISNUMBER(F5:I5*1), F5:I5&" "&F3, F5:I5); F6:I}, 
 COLUMN(G5:I5)-(COLUMN(F5)-1), 0))}), "order by Col1", 1)), " AOpen", " Open"))

0

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE({{
 IF(ISNUMBER(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")*1), 
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")&" AOpen", 
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N limit 0")); QUERY(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"), "offset 1", 0)}, 
 IFNA(VLOOKUP(INDEX(
 QUERY('Raw Data'!A:N, "select B,count(A) where N >= date '2020-02-01' group by B pivot N"),,1), {IF(ISNUMBER(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")*1), 
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")&" Closed", 
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H limit 0")); QUERY(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H"), "offset 1", 0)}, 
 SEQUENCE(1, COLUMNS(
 QUERY('Raw Data - Closed'!A:N, "select A,count(B) where H >= date '2020-02-01' and H <= date '2020-04-01' group by A pivot H"))-1, 2, 1), 0))}), "order by Col1", 1)), " AOpen", " Open"))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks for the quick response - unfortunately i realise that my initial question wasn't clear - what i need to do is report directly from the "raw" data sheets. The 2 tables I had there was simply to demonstrate that i had figured out how to query the tables (the actual query formula is much more complex than the sample one) – Gazing South May 06 '20 at 22:55
  • many thanks - it goes me the desired results - im pulling apart the formula now - can you help me understand the logic of it - it appears to be doing some string substitutions for the headers, but im strill struggling to understand the logic – Gazing South May 07 '20 at 00:33
  • if you refer to SUBSTITUTE function then yes, QUERY is sorted alphabetically which means that C comes before O, but you want it sorted Open and then Close so we add A before O, then sort it and then substitute AO... with O... – player0 May 07 '20 at 01:17