7 Replies Latest reply on Aug 22, 2016 4:19 AM by beverly

    Report that only Shows past 12 months

    gmotta

      I'm a bit stuck here....

       

      I have a table with Dates ranging from 2012 -2016....How do I create a report  that shows only the past 12 months....when I try creating the report....it always shows all of the date.

       

      Thanks in advance.

        • 1. Re: Report that only Shows past 12 months
          philmodjunk

          Perform a find. This can be a manual find or in a script.

           

          In a script:

          enter find mode [ ] - clear the pause

          set field [ yourtable::datefield; ">" & get ( currentDate ) - 365 ]

          perform find [. ]

          • 2. Re: Report that only Shows past 12 months
            fmpdude

            Since a year doesn't always have 365 days, and if you're really picky, and I am given odd bugs customers might report like "the date is off" this (leap) year, you could create a function that would also take into account leap years (divisible by 100 and not divisible by 400).


            Seems like an obvious choice for a CF.

            • 3. Re: Report that only Shows past 12 months
              alangodfrey

              All depends what you mean by 'past 12 months'.  If this is 21st August 2016, do you mean 1st August 2015 to 31 July 2016?  Or 22nd August 2015 to 21 August 2016?

               

              This calculation will return the date a year ago (depending on what you mean, of course):

              Date ( Month ( DateStart ) - 12 ; Day ( DateStart ) ; Year ( DateStart ) )

               

              For example, set the Start Date to be 28/2/2015 and it will return the year-previous date as 28/2/2014.  Set it as 29/2/2016 and it will return 1/3/2015.

              If that's not what you meant by 'a year ago' you could adjust it.

               

              This is a helpful post:

              Creating Find Set based on date ranges - Finding & Searching - FMForums.com

              • 4. Re: Report that only Shows past 12 months
                gmotta

                Yep, that was exactly what I was looking for! Thank you Philmodjunk! I had tried that before....but I forgot to type  in the "&"....That's why it was not working.

                 

                I have 2 other questions.... should be easy.

                 

                How can my report display the currently selected User on a target field  on another layout?...So when I use the script "Go to layout:MyReport" it shows the data only for that User...I'm having to manually find the User.

                 

                And Why is my page number showing as "?" at the end of the report?

                 

                Sorry if the questions seem a bit dumb...I'm new to filemaker. And thanks so much for the help!

                • 5. Re: Report that only Shows past 12 months
                  beverly

                  in those cases, I use:

                  Date 

                        ( Month ( Get ( CurrentDate ) )

                        ; Day ( Get ( CurrentDate ) )

                        ; Year ( Get ( CurrentDate ) - 1 )

                        )

                  It handles leap years/days very well. If today is 2/28 (rare), then last year would be 2/27 or 3/1 (I haven't tested).

                  And YES! a cf (custom function would help).

                  beverly

                  • 6. Re: Report that only Shows past 12 months
                    alangodfrey

                    Beverly - using the alternative I posted it calculates 'a year ago' from 2/29 (the 'rare' one...) as 3/1.

                    I am regularly amazed how powerful the FM Date function is.  Almost Black Magic.

                    • 7. Re: Report that only Shows past 12 months
                      beverly

                      Thanks, alan! I knew I should have tested for

                      Date ( 3 ; 0 ; Year ( Get ( CurrentDate ) ) ) // last day of Feb.

                       

                       

                      my point was that the math can be done on each part of the

                           date ( month +/- ; day +/- ; year +/- )

                      FileMaker is smarter than I am to get the correct date!

                      beverly