12 Replies Latest reply on Aug 18, 2017 7:11 PM by philmodjunk

    Help needed for unusual MonthSort equation

    springer01

      I have a VISA account in which  the accounting period is from the 5th of any Month thru the 4th of the succeeding Month.

      I'd like to calculate the Month to be from the 5th of any Month thru the 4th of the succeeding Month, so it can be used in a MonthSort calculation. Field Name for the Date is d_date

      I want that for comparison to my VISA statement so as to detect any data-entry errors on my part.

      I've tried several equations with no success. Any help will be appreciated.

        • 1. Re: Help needed for unusual MonthSort equation
          FabriceNordmann

          Unless I misunderstood :

           

          month ( date ) - case ( day ( date ) < 5 ; 1 )

          • 2. Re: Help needed for unusual MonthSort equation
            philmodjunk

            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

              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

                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 Sub-summary Layout:

                Fields.png

                And after sorting by d_month_sort, I get this:

                Sub-summary.png

                 

                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

                  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

                    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 error-mag with the 'm' highlighted, as follows:

                    The 'm' error in  equation.png

                    'm' is a Field Name; I don't understand the error. When I attempt to change it, e.g. deleting the m , the error-msg 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

                      There's a semi-colon 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

                        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 semi-colons 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 Sub-Summary break at the end of each calculated Month to show the data in the Sub-summary 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 Sub-summary 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 Sub-summaries at the end of each calculated Month and the incorrect MonthName.

                        Any suggestions?

                        • 9. Re: Help needed for unusual MonthSort equation
                          philmodjunk

                          Problem is that It doesn't show the Sub-Summary break at the end of each calculated Month to show the data in the Sub-summary 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

                            Here is my layout;

                            Screen Shot 2017-08-18 at 6.58.24 PM.png

                            In answer to your questions:

                            You're correct that it doesn't show the Sub-summary 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 Sub-summary layout. It is your equation as delineated in my long msg above, using my field-names.

                            The date field shown in the Sub-summary part is d_month_name, shown at the top of the Sub-summary 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

                              "You're correct that it doesn't show the Sub-summary 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 Sub-summary layout. It is your equation as delineated in my long msg above, using my field-names."

                               

                              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

                                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.