Search code examples
databasegoogle-sheetsstacked-chart

Google Sheets Stacked Chart with data structured as a DB table

On the paper it was simple to do, but at the end I'm stucked. Herebelow the chart I would like to produce (I took an easy example to understand the objective to reach):

enter image description here

On this chart we see that the series for each product are well colored and well structured for each cart.

Now, let's jump on my problem: the data structure. In my world, data is coming from a database, so the data are structured like that:

enter image description here

I managed to aggregate the values but I loose colors of my series and I need absolutely to identify visually the different product volumes.

Is there any possiblity to reach the first stacked chart layout but with the second dataset format ?

Solution

  • Yes, transfer your data and chart from there (I don't think there's a native solution for this, but happy to learn otherwise).

    For example, use =QUERY([data],"select [cart], sum([qty]) group by [cart] pivot [product] label sum([qty]) ''",1) replacing the terms in the brackets with the respective column letters (e.g. B if cart is in column B).