
1. Re: Help needed for unusual MonthSort equation
FabriceNordmann Aug 9, 2017 5:00 AM (in response to springer01)Unless I misunderstood :
month ( date )  case ( day ( date ) < 5 ; 1 )

2. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 9, 2017 5:03 AM (in response to springer01)Let ( [ dt = yourDateFieldHere ;
dy = day ( dt )
m = month ( dt )];
Date ( m ( dy < 5 ) ; 1 ; year ( dt) )
) // let
use a result type of date

3. Re: Help needed for unusual MonthSort equation
TomHays Aug 9, 2017 5:34 AM (in response to springer01)The following calc will generate a YYYYMM value (eg, 201708 for today) which can be used for sorting.
Jan 4, 2017 ==> 201612
Jan 5, 2017 ==> 201701
Aug 3, 2017 ==> 201707
Aug 9, 2017 ==> 201708
Dec 4, 2017 ==> 201711
Dec 5, 2017 ==> 201712
Let([
~monthOffset = If( Day(d_date) < 5; 1; 0);
~aDate = Date(Month(d_date) + ~monthOffset; 1; Year(d_date))
];
Year(~adate) & Right("0" & Month(~adate); 2)
)
Tom

4. Re: Help needed for unusual MonthSort equation
springer01 Aug 9, 2017 12:02 PM (in response to philmodjunk)Altho' I was an EE and very good at math I have difficulty understanding these equations...I can't get them to work.
Here are my field names and their calculations:
dy_day Calculation = Day (d_date)
m_month Calculation = Month (d_date)
n_trans_G_0 (transactions > zero) and a similar one L_0 for < 0.
If ( n_transaction > 0 ; Sum ( n_transaction ; "0" ))
d_sort_month Calculation = Date (m_month  ( Day ( d_date ) < 5 ) ; 1 ; Year (d_date )) // let
I also have Fields for Sum of Transaction > 0; & Sum of Transaction < 0; so I can compare these data with my VISA Report.
Here's my Subsummary Layout:
And after sorting by d_month_sort, I get this:
Note that the Month is a number which, to me, is incomprehensible. Also, only the month of July shows; the total transactions for July shows only the last transaction of the month; the Sums of Transactions,> & < 0 are incorrect as compared to my VISA Statement for the period 05 Jul 17 thru 04 Aug 17.
I'm at my wits end and frustrated that I can't seem to understand the details of the equations you folks have presented. I'm really thankful for your attempts to help.

5. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 9, 2017 1:13 PM (in response to springer01)First, your example is missing the Let function at the beginning, that's probably a typo. I also encourage you to also use returns to format the calculation similar to what I did. It makes it easier to read.
I also specified that the result type is to be DATE and not number. This should produce a date for the first day of the month, but for the first few days, it will be the first of the preceding month.

6. Re: Help needed for unusual MonthSort equation
springer01 Aug 10, 2017 1:49 PM (in response to philmodjunk)philmodjunk: Continuing problems! In attempts to implement your equation. I created Fields as used in your equation:
dt Date Date of Transaction
m Calculation = Month ( dt )
dy Calculation = Day ( dt )
d_sort_month Date Calculated value your equation; as follows:
Let ( [ dt = d_date ;
dy = day ( dt )
m = month ( dt ) ] ;
Date ( m  ( dy < 5 ) ; 1 ; year ( dt)
) // let
However, I get an errormag with the 'm' highlighted, as follows:
'm' is a Field Name; I don't understand the error. When I attempt to change it, e.g. deleting the m , the errormsg changes to highlighting 'year' with the same msg.
I'm totally confused, besides not understanding the syntax of the equations.
May I pester you again for help? What error(s) have I made?

7. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 10, 2017 2:47 PM (in response to springer01)There's a semicolon missing after the end of the ( dt ) in the second row. I left it out of my example as well.

8. Re: Help needed for unusual MonthSort equation
springer01 Aug 18, 2017 4:36 PM (in response to philmodjunk)Mr. philomodjunk:
My delay in posting a response is because I've been trying the several equations posted in response to my query; NONE OF WHICH has been successful. As a result I've downloaded several tutorial documents from the FMP Pro Help web pages, including: FMP 12 Equation Operators; FMP 12_Functions Reference and MANY instructions from the Help pages in an attempt to understand the equations & Functions, and have tried to correct the errors in the equations y'all have offered, to no avail.
Several of the equations include symbols not defined in any of the documents I've downloaded, e.g.: " , single $, single ", [ ] , and several more, which I don't understand. These, and their explanations, should have been included in the 'Equation Operators' document.
Best Functions example is philmodjunk's equation for MonthSort:
Let ( [ dt = yourDateFieldHere ;
dy = day ( dt ) ;
m = month ( dt )];
Date ( m ( dy < 5 ) ; 1 ; year ( dt) )
) // let
use a result type of date
( I applied his corrections re semicolons stated in his later msg ).
My version, to use my fields in your equation for MonthSort function is:
d_month_sort Date Options:
d_month_sort =
Let ( [ dt = ( d_date ) ;
dy = Day ( d_date ) ;
m = Month ( d_date ) ] ;
Date ( m  ( dy < 5 ) ; 1 ; Year ( d_date ) )
) // let
I sorted by : MonthSort
d_date
t_category
✓ Keep records in sorted order (I've tried both checking and not)
Problem is that It doesn't show the SubSummary break at the end of each calculated Month to show the data in the Subsummary layout. I have added five records to the list with transaction dates of August 5th & 11th, to see how it handled dates beyond the cutoff of the 4th of the month. It succeeded by including dates of August 1st, 2nd, 3rd, but not the 5th & 11th.
Also, it shows the Subsummary at the top of the list of records per the layout and shows the MonthName August at the end of the list, which transaction date is August 03; which indicates that it is ending the Statement Month as required ... by the 4th of the succeeding month, but the MonthName is incorrect; it should be July.
If I could only figure out why it is not showing the Subsummaries at the end of each calculated Month and the incorrect MonthName.
Any suggestions?

9. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 18, 2017 4:47 PM (in response to springer01)Problem is that It doesn't show the SubSummary break at the end of each calculated Month to show the data in the Subsummary layout.
And what is the "sorted by" field for the sub summary part?
and the incorrect MonthName.
And which date field are you using to show the month name? (And do you want the month for the actual date or the month for the group that it is part of?)

10. Re: Help needed for unusual MonthSort equation
springer01 Aug 18, 2017 5:32 PM (in response to philmodjunk)Here is my layout;
In answer to your questions:
You're correct that it doesn't show the Subsummary break at the end of each calculated Month; that is one of my big problems!
The '"sorted by" field is d_month_sort, shown on the Subsummary layout. It is your equation as delineated in my long msg above, using my fieldnames.
The date field shown in the Subsummary part is d_month_name, shown at the top of the Subsummary layout.
It's field is: d_month_name Calculation Number
Case ( Month ( d_date )
= Month ( d_date ) ;
MonthName (d_date ) ;
MonthName (d_date ) + 1 )
The MonthName is incorrect (see my above msg) in that it is calculation from the 5th of the month thru the 4th of the succeeding month and shows August instead of July.
I hope you can inform me of my errors. I'm at my wits end ... have worked days on this,

11. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 18, 2017 5:59 PM (in response to springer01)"You're correct that it doesn't show the Subsummary break at the end of each calculated Month; that is one of my big problems!"
I was quoting you to explain why I was asking this question.
"The '"sorted by" field is d_month_sort, shown on the Subsummary layout. It is your equation as delineated in my long msg above, using my fieldnames."
Either this sub summary does not specify this field as the break value, the records aren't sorted correctly, or the calculation is not returning the same date for every record in the group. Otherwise the records would group correctly with the sub summary part.

12. Re: Help needed for unusual MonthSort equation
philmodjunk Aug 18, 2017 7:11 PM (in response to springer01)Here's a quick demo file. I just copy pasted the calculation from this discussion, added the missing semi colon and inserted my date field. Then threw together a simple summary report layout with sub summary parts. There's a button that sorts the records in to the desired order: First by date sort calculation field, then by the actual date field.
And there's a field that shows the name of the month as well as the year. It's the same calculation field with some date formatting.