Please have a look at this google sheet here.

It has a pivot table with 3 columns:

A period (for example: 2016/01), a category and a number. I would like to do a chart that looks like this:

For each period, have a breakdown of the number for each category (one bar for each category).

Unfortunately, every attempt failed so far since I can't even aggregate the data properly in the pivot table:

As you can see, the same "period" is repeated many times even within the same category. The same happens with "category" - it doesn't aggregate the data.

Should I fix the pivot table: if so, how? Or should I just ask the bar graph to aggregate the data? So far, any attempt hasn't worked and only displays scrambled data.

I'm sorry this question sounds a lot like "please do this for me": as you can see, I've managed to aggregate the data properly in the sheet "Simple pivot table". However, as soon as a third variable is added, I'm completely at a loss.

1 Answer 1


Pivot table was wrangled because of how I compute Period. It used to be just a copy of Date, I'd then use Sheets Data -> Number -> Format to change how it's displayed (removing the day, leaving just month and year). This is a stupid workaround since the pivot table knows the underlying data, even if the cell doesn't display it.

Replaced this trick with =TEXT(DateCells, "yyyy/mm") and now the data properly aggregates.

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.