[Goal] I want to create a table with the Query Function where it counts the number of 'Drivers' for each month in a dynamic manner. Meaning that when the data (example sheet is called 'Data') is updated, it'll be updated automatically as well.
[What I was able to do so far] I was able to create a table with the Query Function, however, it only displays 1 column worth of Months when I want to show up to 4 months. And I also want to show the recent months from the left and the older months on the right.
[Formula that I have so far]
=QUERY(Data!$A:$B,"
SELECT B,
Count(B)
Where B != '' AND MONTH(A)=MONTH(DATE'"&TEXT(A2,"YYYY-MM-DD")&"')
Group By B
Pivot A
Order By B asc
Label B 'Drivers', Count(B) '"&TEXT(A2,"MMMM YYYY")&"'",1)
[Issue that I'm facing] I've tried specifying the date range like the below, however there are 2 problems.
- The date format is not mmmm yyyy (Example: May 2022) and it'd show as: 2022-2-1 May 2022
- The months are ordered in an ascending manner (Example: 2022-2-1, 2022-3-1, 2022-4-1) instead of descending (Example: 2022-4-1, 2022-3-1, 2022-2-1)
So I'm not sure what I need to do to fix this. Hopefully I can have support.
Where B != '' AND MONTH(A)<=MONTH(DATE'"&TEXT(A2,"YYYY-MM-DD")&"')
AND MONTH(A)>=MONTH(DATE'"&TEXT(EDATE(A2,-3),"YYYY-MM-DD")&"')
[Sample Sheet] https://docs.google.com/spreadsheets/d/1AJYTRga9-dXbj64nl4RfpDKs5JYSFwP2SiR7v7gAhMI/edit#gid=1297239620
CodePudding user response:
=ARRAYFORMULA(REGEXREPLACE(""&TRANSPOSE(QUERY(TRANSPOSE(
QUERY({Data!A:B, TEXT(Data!A:A, "yyyymmdd×MMMM yyyy")},
"select Col2,count(Col2)
where Col2 != ''
AND MONTH(Col1)<=MONTH(DATE'"&TEXT(A2,"YYYY-MM-DD")&"')
AND MONTH(Col1)>=MONTH(DATE'"&TEXT(EDATE(A2,-3),"YYYY-MM-DD")&"')
group by Col2
pivot Col3", 1)),"order by Col1 desc")),"^(.*×)", ))
Reference:
How do I change the date format in a Google Sheets query pivot table with date filters?