0

I'm trying to group the data into a table so that I can group the DATE column into a month + year and at the same time differentiate the segment.

enter image description here

In MySQL something like:

SELECT CONCAT(YEAR(Date), '-',MONTH(Date)) AS MonthYear, Segment, SUM(Price) FROM table GROUP BY CONCAT(YEAR(Date), '-',MONTH(Date)), Segment

From this I then need to create a stacked graph. I was looking for various tutorials but to no avail.

4
  • 1
    Welcome to Web Applications. There are a lot of ways to do this, i.e. using pivot tables, formulas using functions like QUERY, using Google Apps Script... Have you already searched this site? Have you used pivot tables before? – Rubén Dec 8 '20 at 17:07
  • Hello, i want solving it with pivot table. Yes i before tried pivod tables, but no with "multiple grouping". I i tried this, pivot makes me for each "Segment" another column and then I can't create a graph from that – TOP MUSIC Dec 8 '20 at 17:53
  • but in the end I can have any other solution – TOP MUSIC Dec 8 '20 at 17:55
  • 1
    Related: How to group data by month in Google Sheets (Duplicate?) – Rubén Dec 8 '20 at 19:39
0

Use this formula, then select the output and insert a chart (Stacked Column)

=query({B2:B7,ArrayFormula(EOMONTH(A2:A7,0)),C2:C7},"Select Col2, Sum(Col3) group by Col2 pivot Col1 format Col2'MMM-YYYY'")

Logic:

  • Query data

    • three separate ranges contained within curly brackets and separated by commas (array values)
    • the month/year value is derived by using EOMONTH(), and the date format is generated in the query by format Col2'MMM-YYYY'
    • the data is grouped by date
  • Pivot Table

    • the selected data are: Month/Year and summed Segment value
    • the pivot value is the Segment.

Data, Pivot Table and Stacked Column graph

Data, Pivot Table and Stacked Column graph


Credit:

Applied the logic described in infoinspired.com: How to Group Data by Month and Year in Google Sheets

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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