Sorting by chronological not alphabetical months in GROUPBY.

[Shortmeister1]

Merry Christmas all

My sample table looks like this:

Date

16/05/2024

07/06/2024

11/06/2024

21/06/2024

01/07/2024

13/07/2024

23/07/2024

28/09/2024

29/09/2024

A small formula using the new GROUPBY function.

=GROUPBY(
TEXT(tblV[Date],"mmm-yy"),
TEXT(tblV[Date],"mmm-yy"),
COUNTA,0,0)

The problem with this is that April becomes the first month in the year, whereas I want the year to start with January.

Easy to do in a Pivot table, but surely shouldn't be too difficult to replicate it using GROUPBY? (I don't want to create a helper column)

Any takers?

Thanks
Martin

 

[Fluff]

How about

=DROP(GROUPBY(HSTACK(EOMONTH(+Tblv,0),TEXT(Tblv[Date],"mmm-yy")),TEXT(Tblv[Date],"mmm-yy"),COUNTA,0,0),,1)

Or

=GROUPBY(EOMONTH(+Tblv,0),TEXT(Tblv[Date],"mmm-yy"),COUNTA,0,0)

and format the 1st col as mmm-yy

 

[Dave Patton]

I added 2 dates to your table.
Try either of the following

T202412a.xlsm
	A	B	C	D	E	F	G	H
1
2	Date
3	16-May-24			2401	1		2401	1
4	7-Jun-24			2404	1		2404	1
5	11-Jun-24			2405	1		2405	1
6	21-Jun-24			2406	3		2406	3
7	1-Jul-24			2407	3		2407	3
8	13-Jul-24			2409	2		2409	2
9	23-Jul-24			Total	11		Total	11
10	28-Sep-24
11	29-Sep-24
12	5-Jan-24
13	4-Apr-24
4d

Cell Formulas
Range	Formula
D3:E9	D3	=LET(d,TEXT(Tblv[Date],"yymm"),GROUPBY(d,--d,COUNT))
G3:H9	G3	=LET(d,TEXT(Tblv[Date],"yymm"),GROUPBY(d,d,COUNTA))
Dynamic array formulas.

 

[Dave Patton]

What is your expected result?
If you do not want to show months with 0, filter the results.

T202412a.xlsm
	A	B	C	D	E	F	G
1
2	Date
3	16-May-24		2401	1		Jan	1
4	7-Jun-24		2404	1		Feb	0
5	11-Jun-24		2405	1		Mar	0
6	21-Jun-24		2406	3		Apr	1
7	1-Jul-24		2407	3		May	1
8	13-Jul-24		2409	2		Jun	3
9	23-Jul-24		Total	11		Jul	3
10	28-Sep-24					Aug	0
11	29-Sep-24					Sep	2
12	5-Jan-24					Oct	0
13	4-Apr-24					Nov	0
14						Dec	0
15
4d

Cell Formulas
Range	Formula
C3:D9	C3	=LET(d,TEXT(Tblv[Date],"yymm"),GROUPBY(d,d,COUNTA))
F3:G14	F3	=HSTACK(Months,LET(d,Tblv[Date],IFNA(VLOOKUP(SEQUENCE(12),GROUPBY(MONTH(d),d,COUNT),2,0),0)))
Dynamic array formulas.

 

[djclements]

Another variation could be:

=LET(
    a, GROUPBY(EOMONTH(+tblV[Date],0),tblV[Date],ROWS,0,0),
    HSTACK(TEXT(TAKE(a,,1),"mmm-yy"),DROP(a,,1))
)

Note: the values argument of GROUPBY can simply be tblV[Date] in this case (it is unnecessary to perform an additional TEXT calculation on the entire column again).

Merry Christmas!

 

[Dave Patton]

I like djclements post #5

Included are versions that I had prepared.
The second option uses helper named arrays for the month numbers and the month short names

T202412a.xlsm
	A	B	C	D	E	F	G
1
2	Date
3	16-May-24		2401	2		Jan	2
4	7-Jun-24		2404	1		Apr	1
5	11-Jun-24		2405	1		May	1
6	21-Jun-24		2406	3		Jun	3
7	1-Jul-24		2407	3		Jul	3
8	13-Jul-24		2409	2		Sep	2
9	23-Jul-24		Total	12			12
10	28-Sep-24
11	29-Sep-24
12	5-Jan-24
13	4-Apr-24
14	20-Jan-24
15
4d

Cell Formulas
Range	Formula
C3:D9	C3	=LET(d,TEXT(Tblv[Date],"yymm"),GROUPBY(d,d,COUNTA))
F3:G9	F3	=LET(a,LET(d,MONTH(Tblv[Date]),GROUPBY(d,d,COUNTA)),HSTACK(XLOOKUP(CHOOSECOLS(a,1),Mons,Months,"",0),CHOOSECOLS(a,2)))
Dynamic array formulas.

 

[Dave Patton]

Another choice
Custom format column C to your preference; I used "mmm".

T202412a.xlsm
	A	B	C	D
1
2	Date
3	16-May-24		Jan	2
4	7-Jun-24		Apr	1
5	11-Jun-24		May	1
6	21-Jun-24		Jun	3
7	1-Jul-24		Jul	3
8	13-Jul-24		Sep	2
9	23-Jul-24		Total	12
10	28-Sep-24
11	29-Sep-24
12	5-Jan-24
13	4-Apr-24
14	20-Jan-24
4d

Cell Formulas
Range	Formula
C3:D9	C3	=LET(d,Tblv[Date],GROUPBY(d-DAY(d)+1,d,COUNT))
Dynamic array formulas.

 

[Shortmeister1]

Thanks for all the replies guys. Really appreciated.

Surprising that MS didn't add different kinds of sort to this functionality, but definitely have some hacks to work with.

Cheers!

 

[Shortmeister1]

=GROUPBY(EOMONTH(+Tblv,0),TEXT(Tblv[Date],"mmm-yy"),COUNTA,0,0)

Dear Mr Fluff

One question that's puzzling me. Why the + symbol in EOMONTH? I can see that it errors without it, but I can't see why.

Cheers

 

[Fluff]

EOMONTH does not like being given a range, but is happy with an array & the + sign converts the range into an array.