Query/Pivot Function: Header Title Descending Order & Month Format mmmm yyyy-CodePudding

[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?

Sort Query Pivot Table - Google Sheets