Pivot table loses year, quarter, month grouping if source date range is 1 year or less

[jeff_cp]

I'm hoping this is just a setting I'm overlooking. I have a spreadsheet where I'm pulling in data from a query. I have a start and end date parameter. If I extend the date range to span multiple years, I can group the pivot table on years, quarters and months. However, if I change my date range to a year or less, I lose the grouping altogether. I'd like to maintain that grouping if possible regardless of the date range.

For example, let's say I run the query for 3/27/20 thru 3/31/20. I'd like the pivot table to show a year grouping of 2020, a quarter grouping of 1 and a month grouping of March. That way, no matter what my date range, the pivot table will always be grouped by year, quarters and months.

Thanks in advance for any assistance.

 

[jeff_cp]

I found a work around. In the table I'm using to compile the pivot table, I added 3 fields...one for month, one for quarter and one for year. I'm using those fields as my rows in the pivot table and since those fields are available for each record, I'm no longer losing my "auto" grouping.

For the month field, I'm using:
=TEXT(A10,"mmmm")

For the quarter field, I'm using:
=IF(ROUNDUP(MONTH(A10)/3,0)=1,ROUNDUP(MONTH(A10)/3,0)&"st Quarter",IF(ROUNDUP(MONTH(A10)/3,0)=2,ROUNDUP(MONTH(A10)/3,0)&"nd Quarter",IF(ROUNDUP(MONTH(A10)/3,0)=3,ROUNDUP(MONTH(A10)/3,0)&"rd Quarter",IF(ROUNDUP(MONTH(A10)/3,0)=4,ROUNDUP(MONTH(A10)/3,0)&"th Quarter",""))))

For the year field, I'm using:
=YEAR(A10)