Creating a single graph using two queries with differing dates

Question

I have two different queries with the following results and dates. I would now like to create a query that will allow me to create 1 single graph:

Desired query output:

The blank cells could be 0 if needed.

Graph would look similar to the one above, but this example is missing Aug 31st, which is what I am trying to solve.

user_5632629 · Answer

the line chart is based on series so you need to reorganize your data like this:

=QUERY({QUERY(A2:B10, "select A,B,'1'", );
        QUERY(C2:D10, "select C,D,'2'", )}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3", )

and then get a line chart:

Creating a single graph using two queries with differing dates

1 Answers