SORT & SUBTOTAL BY MONTH

[jordioni]

I have records with a date stamp that I would like to sort and subtotal by month. can anyone tell me how to do that?

Thank you

[comment]

Create a calculation field cMonth (result is date) =

DateStamp - Day ( DateStamp ) + 1

Sort and subtotal by this field.

Note:

When you put the field on the layout, format it to display only the month and the year (in Format -> Date... ).

[IdealData]

You'll need a separate field to hold the month number and another layout with a SubSummary part on it.

1. Calculation: MONTH_NUMBER = Month ( date_stamp_field )

2. Summary field: GENERAL_SUMMARY (Total of amount_field)

3. Layout: MUST incorporate a "Sub summary when sorted by...MONTH_NUMBER)

4. Place the GENERAL_SUMMARY in the SubSummary section

5. Place the amount_field in the BODY section (just to confirm it's all ok)

Then Find and sort your records by MONTH_NUMBER

[comment]

I prefer to calculate a DATE that is common to all records of the same month, because:

a) it separates records of August 2005 from those of August 2004, etc.;

: it sorts correctly;

c) can be easily formatted to display in any way you like.

Your suggestion using Month() does (:, but not (a) and ©.

[jordioni]

Sorry to be so lame but I don't understand what goes into the formula... my timestamp is a field called Order date. when I create the calculation cMonth, what exactly is the calculation? Your patience is appreciated.

[comment]

The formula:

Order date - Day ( Order date ) + 1

This returns the date of the first day of month of Order date (e.g. for all orders of this month, the result will be Aug 1, 2005). Make sure the result is set to Date.

[LaRetta]

I think it might be a timestamp field?

[comment]

Wait - did you say timestamp?

If your Order date field is of type timestamp, you will need to extract the date first. Here's the formula:

Let (

dt = GetAsDate ( Order date )

;

dt - Day ( dt ) + 1

)

The result is Date again.

[jordioni]

Got it. Thanks!