Sort based on month then separated by year

[Matthew R White]

I have a table that first sorts all records by month, Jan, Feb, Mar etc.

Within those months i have multiple records of different years, mainly 2014 and 2013.

I'd like to separate the years within the month so for example

 

January

2014 - Year Separator

1/5/2014

1/10/2014

1/15/2014

2013 - Year Separator 

1/5/2013

1/10/2013

1/15/2013.

 

February

 

etc etc

 

Any advice?

[comment]

Do you already have fields for month and for year?

[Matthew R White]

Everything currently revolves around my field (effective date)

[comment]

Well, first you'll need the two fields to sort by, so define:

 

 

cYear (result is Number) =

Year ( effective date )

cMonth (result is Number) =

Month ( effective date )

Sort your records by cMonth and by cYear. Use a layout with two leading sub-summary parts, one for each of the two calculation fields.

 

 

---

 

A variation in case you want to show labels with month names: make cMonth =

MonthName ( effective date )

(result needs to be Text). Define a custom value list listing the 12 month names, and use it when sorting by cMonth.

[LaRetta]

 

Sort your records by cMonth and by cYear. Use a layout with two leading sub-summary parts, one for each of the two calculation fields.

 

 

I believe to get the results above in post #1, the order might be important to mention.  The first leading part would be cYear and seconding leading part would be cMonth.  Then be sure to sort descending on cYear, ascending on cMonth and ascending on EffectiveDate.

[comment]

I believe to get the results above in post #1, the order might be important to mention.  The first leading part would be cYear and seconding leading part would be cMonth.  Then be sure to sort descending on cYear, ascending on cMonth and ascending on EffectiveDate.

 

It seems to me that the results in post #1 are sorted (and grouped) by month first, then by year. It looks like they want to compare  monthly figures between the years.

[LaRetta]

Oh yes, I misinterpreted.  :-)