# SORT & SUBTOTAL BY MONTH

This topic is 6672 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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... ).

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

I think it might be a timestamp field?

##### Share on other sites

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.

Got it. Thanks!

##### Share on other sites

This topic is 6672 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account