6 Replies Latest reply on Dec 27, 2013 4:14 PM by FileMakerNovice

    Fiscal range search

    FileMakerNovice

      Title

      Fiscal range search

      Post

           Hello,

           I am looking for a solution on how to do an automatic search given a fiscal month.  I would like to have a pull down list displaying the last 12 fiscal months:  2013P10, 2013P11, 2013P12, 2014P1, etc.  Once one of these is selected, I want the search to perform for the date range of these.

           I am familiar with searches, lists and relationships but can't figure a starting point.  My first thought is to create a table with Name, Start, End, Active.  Our Fiscal Year starts in October.  So October would be 2014P1, 9/29/2013, 11/1/2013, True.  I could then create a list with a trigger to do a search when 2014P1 is selected.

           Is this the best way or is there an easier way?  Additionally, is there a way to automate the creation of the fiscal list?

        • 1. Re: Fiscal range search
          philmodjunk

               2013P10represents the 10th month of Fiscal year 2013?

               What data defines which dates are represented by 2013P10?

               A month is still a calendar month, but not starting with January for the first month of the year?

               Assuming that is the case, do you want to "hard wire" this to a specific date as the first day of the fiscal year or do you want it to be based on a date entered into a table such that you can change the fiscal year by editing the date in this field?

          • 2. Re: Fiscal range search
            FileMakerNovice

                 Thanks for the response.

                 Correct.  2013P10 is the 10th period in the 2013 fiscal calendar.  The 10th month in our fiscal year is July.  2014P1 is October but contains more than just October dates, as each fiscal month starts on a Sunday and ends on a Saturday.

                 I believe this is similar across all businesses:  The first month in each quarter is 5 weeks long.  Therefore, the 1st, 4th, 7th and 10th period (month) are 5 weeks long.(October, January, April and July)  The rest are 4 weeks.  The fiscal month always begins on a Sunday and ends on a Saturday.

                 My tables are a list of dates and names.  My goal is to be able to select the fiscal stamp (ex 2013P01) and then a search is performed from start to end. I could create the list manually, but want a more automatic approach.  Or... if there is some other way around the problem, would welcome any possible solution.

            • 3. Re: Fiscal range search
              philmodjunk
                   

                        2014P1 is October but contains more than just October dates, as each fiscal month starts on a Sunday and ends on a Saturday.

                   Does this mean that 2014P1 incorporates all dates from Oct 29, 2013 to Nov 2, 2013?

                   This looks like something we can set up with a calculation that takes the date in a date field and returns the year and period. Then this becomes a simple case of performing a find or matching to related records to pull up the records that fall in the period selected from the value list, but the devil is in the details when it comes to setting up that calculation...

              • 4. Re: Fiscal range search
                FileMakerNovice

                     Good thinking.  So basically a formula that takes 2014P01 and results in 9/29/2013.  And then another formula that results in 11/2/2013.  Then have a value list that has the last 12 fiscal periods.  Good food for thought.  I think I have a lot to work on from here.

                     Oh... I could make a table with 12 records that gives the label (2014P01) by extracting it from the current date.  I could then find the fiscal start and end date of that period.  THEN I could make a value list from this table.

                     NOW is the fun part of figuring out a formula to figure out what exactly constitutes the start and end of a fiscal period. 

                • 5. Re: Fiscal range search
                  philmodjunk

                       Not quite.

                       What I'm after is a formula that takes any date from Oct 29, 2013 to Nov 2, 2013 and returns 2014P1, and the next 4 week date range would return 2014P2 and so forth...

                       I managed to get this far:

                       Let ( [ D = YourDateFieldHere ;
                                 Y1 = Year ( D ) ;
                                 Fs1 = Date ( 10 ; 1 ; Y1 ) ;
                                 Fs2 = Fs1 - DayOfWeek ( Fs1 ) ; // Sunday start of Fiscal year in same calendar year as D
                                 Y   = If ( Fs2 < D ;  Y1 + 1 ; Y1 ) ;
                                 Fs = Date ( 10 ; 1 ; Y - 1 ) - DayOfWeek ( Date ( 10 ; 1; Y - 1 ) ) ; // First day of Fiscal Year, Sunday
                                 P = Case ( D < ( Fs + 35 ) ; 1 ;
                                            D < ( Fs + 63 ) ; 2 ;
                                            D < ( Fs + 91 ) ; 3 ;
                                            // and so forth for all 12 fiscal periods
                                           )
                                ];
                                 Y & "P" & P
                               )

                       This calculation, however, will be off by one day when D falls in March to September of a leap year.

                  • 6. Re: Fiscal range search
                    FileMakerNovice

                         Thanks again.  Due to the unmentioned relationships that I had, I wanted a dynamic list that I will then create a script to paste the related values.  You pointed my brain right.  Here is what I ended up doing:

                         Created a table with 11 records.  
                         There is a field called monthsAgo, ranging from 0-11.

                         There is a field called "label":

                         Let(
                         [
                         M = If( Month (Get(CurrentDate)) - monthsAgo < 1; 
                              Month (Get(CurrentDate)) - monthsAgo + 12;
                              Month (Get(CurrentDate)) - monthsAgo);
                         Y = If ( M < 10; Year(Get(CurrentDate)); Year(Get(CurrentDate))+1);
                         P = If ( M < 10; M+3; M-9)
                         ];
                         If( P<10; Y&"P0"&P; Y&"P"&P)
                         )
                          
                          
                         There is a field called startDate:
                              Let(
                              [
                              M = If( Month (Get(CurrentDate)) - monthsAgo < 1; 
                                   Month (Get(CurrentDate)) - monthsAgo + 12;
                                   Month (Get(CurrentDate)) - monthsAgo);
                              Y = If ( M < 10; Year(Get(CurrentDate))-1; Year(Get(CurrentDate)));
                              P = If ( M < 10; M+3; M-9);
                              FiscalDayOne = GetAsDate("10/2/"&Y) - DayOfWeek("10/1/"&Y)
                              ];
                              Case(
                              P=1;  FiscalDayOne;
                              P=2;  FiscalDayOne+(5*7);
                              P=3;  FiscalDayOne+(9*7);
                              P=4;  FiscalDayOne+(13*7);
                              P=5;  FiscalDayOne+(18*7);
                              P=6;  FiscalDayOne+(22*7);
                              P=7;  FiscalDayOne+(26*7);
                              P=8;  FiscalDayOne+(31*7);
                              P=9;  FiscalDayOne+(35*7);
                              P=10;  FiscalDayOne+(39*7);
                              P=11;  FiscalDayOne+(44*7);
                              P=12;  FiscalDayOne+(48*7);
                              )
                              )
                               
                              And a field called endDate:
                                   Let(
                                   [
                                   M = If( Month (Get(CurrentDate)) - monthsAgo < 1; 
                                        Month (Get(CurrentDate)) - monthsAgo + 12;
                                        Month (Get(CurrentDate)) - monthsAgo);
                                   Y = If ( M < 10; Year(Get(CurrentDate))-1; Year(Get(CurrentDate)));
                                   P = If ( M < 10; M+3; M-9);
                                   FiscalDayOne = GetAsDate("10/2/"&Y) - DayOfWeek("10/1/"&Y)
                                   ];
                                   Case(
                                   P=1;  FiscalDayOne+(5*7)-1;
                                   P=2;  FiscalDayOne+(9*7)-1;
                                   P=3;  FiscalDayOne+(13*7)-1;
                                   P=4;  FiscalDayOne+(18*7)-1;
                                   P=5;  FiscalDayOne+(22*7)-1;
                                   P=6;  FiscalDayOne+(26*7)-1;
                                   P=7;  FiscalDayOne+(31*7)-1;
                                   P=8;  FiscalDayOne+(35*7)-1;
                                   P=9;  FiscalDayOne+(39*7)-1;
                                   P=10;  FiscalDayOne+(44*7)-1;
                                   P=11;  FiscalDayOne+(48*7)-1;
                                   P=12;  FiscalDayOne+(52*7)-1
                                   )
                                   )

                         The result is a dynamic list from 11 previous periods to current period.(2013P4-2014P3)  The start/end dates are determined by the first day of the fiscal year.  I will now make a script that populates a GLOBAL variable "STARTDATE" and "ENDDATE" from these values.

                         Thanks again.  You helped guide me the right way.