Time series plot with category names as text

Question

Let us say I have data like this for a spend summary sheet:

Date	Amount	Category
03/31/2023	20.0	Food
03/31/2023	23.12	Coffee
03/31/2023	21.01	Movie
04/01/2023	10.0	Coffee
04/01/2023	12.0	Coffee
04/01/2023	13.12	Food
04/02/2023	10.0	Movie

I know how to get an aggregate plot by using the Date and Amount columns.

I would like a line plot of multiple lines, now for each category, within the same graph. i.e. for each category, I have a differently colored aggregate line plot. Here for eg, I'd have 3 different colored lines plot, one for food, one for coffee and one for movie, all within the same plot. I'd also like to have a line plot of all categories combined as well, in the same plot. Is such a thing possible in sheets?

Thanks

Answer 1

One way to do that is to pivot the data so that Food, Coffee and Movie each have their own column, like this:

=query( 
  A1:C, 
  "select A, sum(B) 
    where B is not null 
    group by A 
    pivot C", 
  1 
)

Then plot the result table instead of the original. Use a stacked column chart to view how total develops over time.

If you absolutely want to add a separate totals figure, use this pattern:

=let( 
  data, 
    query( 
      A1:C, 
      "select A, sum(B) 
       where B is not null 
       group by A 
       pivot C", 
      1 
    ), 
  rowsum_, lambda(row, iferror(sum(row) - single(row), "Total")), 
  { data, byrow(data, rowsum_) } 
)

You can move the Total series to the Right axis so that its scale does not diminish the others, but showing totals this way is seldom as informative as using a stacked column chart.