Hello,
I wanted to start using pivot tables with the transaction tab and creating a pivot table where the month column repeats for each year instead. I used the monthly array formula provided in the Tiller community.
=arrayformula(if(row(B1:B)=1,“Month Array”,if(B1:B<>“”,date(year(B1:B),month(B1:B),1),“”)))
From https://help.tillerhq.com/en/articles/662552-how-to-manually-add-month-data
This is how my pivot table looks; attached
I also confirmed that the data format for the column was
“##-mmmm”
I am sure there is something simple that I am doing wrong, but I can’t figure it out.
Welcome expertise, and thank you.
Mic
Hi Mark!
I appreciate your response; thank you.
I am just trying to do a simple pivot chart by month and year. Is that not an option with Google Sheets?
Like this:
To do that, I created a MonthName column, instead of the Month column, using this formula in the row 1 cell: =arrayformula(if(row(B1:B)=1,"MonthName",if(B1:B<>"",text(B1:B,"mm-mmmm"),"")))
MonthName has only 12 unique values even for multiple years.
Month is the first day of every month for every year (e.g. two years in Month will be 24 unique values). That’s why Month keeps repeating for every year in your pivot table.
Hello again. Your support and patience have been excellent. Thank you.
I don’t think I made myself clear. I must update the formula on the monthly column on the translation filled. It needs to auto-populate with the month.
I tried the formula (=TEXT(A2,“mm-mmmm”) ), but it doesn’t auto-populate when new rows are added to the transaction table. I would think if I can get the right formula to add a month column and then create a pivot table, my goal to look like this
Again, I can’t say thank you for enough time, and I hope others can learn from you; in a short time, I will start following you to learn other tips/tricks you can help others with.
Mic
