1

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

1

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.