Sort & Summarise based on Month Year

[VirtualBob]

I'm modifying an Expenses DB template that came with my FM12 app (the only app I have) and it does a monthly summary based on Year ( Due Date ) & " " & Month ( Due Date ) The output is Text - which is sorted in the Sub Summary field.

The summary view based on sub-summary sort works for 80% of the year but for some reason it goes Jan - Oct - Nov - Dec - Feb then the rest is correct month order. I'm not sure what the glitch is. Is there a better workaround that sorts by date and year, so all of 2016 in correct month order then all of 2017 in correct month order etc?

[Steve Martino]

The glitch is that it is sorting as text.   

01, 10, 11, 12, 02 is how it would sort as text.  You need it to sort by date.

i would change the sub summary report to sort by date.  You don't have to show the date in the sub-summary part

 

[VirtualBob]

Understand, what’s the best way to sort by month year (summarised by month) for the sub-summary to sort and display correctly. I often get muddled with this. 

[Steve Martino]

1.  Create a  date field, auto-enter calc (uncheck "Do not replace..."), using your field names, call it zzDateMonthYear:

Due Date - Day (Due Date ) + 1  //Make sure it's a date field.

2.  Make this your field (zzDateMonthYear) for the sub-summary report  (Sub-summary when sorted by), and just put your Year ( Due Date ) & " " & Month ( Due Date ) calculation field in that section.  Make sure your calculation is of type 'Number'.

3.  Sort by the zzDateMonthYear month field.

[LaRetta]

On 11/28/2017 at 7:11 AM, Steve Martino said:

1.  Create a  date field, auto-enter calc (uncheck "Do not replace..."), using your field names, call it zzDateMonthYear:

 

Hi Steve,

There would be no reason to make this stored data unless the User needs the ability to change that date.  Making it a stored calculation is better option. :-)

Edited November 29, 2017 by LaRetta

[VirtualBob]

Thanks for the solution. It worked once I spent an hour working out what it did and that I needed to refresh the existing entries Due Date field! I don't use FMP often enough it seems.