5 Replies Latest reply on Feb 16, 2012 1:10 PM by TSGal





      Description of the issue

      The following calculation returns a 41 not an 11 as I expected.Did I do something wrong? WeekOfYearFiscal(Date;2) 

        • 1. Re: WeekOfYearFiscal
             It would be helpful to know what date you are using!
          • 2. Re: WeekOfYearFiscal


            When I try weekofyearfiscal(date(06;26;2011); 1) the result is 26.   Our fiscal yr begins July 1st, and I need to sort data into weekly summaries.  I was hoping that using the date above the result would be Week 1 (the week ends on Sunday).  I'm playing with this calculation WeekofyearFiscal(Date(06;26;2011); 1)-25 to see if I can get results, but anticipating problems.  Any better ideas? 

            I also need all the data sorted not just one week.   Can you give me any tips?  It's much easier to do in excel at this point.

            • 3. Re: WeekOfYearFiscal

              Reni Smith:

              Thank you for your post.

              If your fiscal year begins July 1st, then you'll need to offset everything by 27 weeks.  Therefore, use:

              Mod ( 27 + WeekOfYearFiscal (<date value> ; 2 ) ; 52)

              The "2" is used since your week begins on Monday ("ends on Sunday").

              Date fields should sort correctly.  Is it possibly your dates are entered into text fields?

              FileMaker, Inc.

              • 4. Re: WeekOfYearFiscal

                Hi,  It was a nice surprise to receive your suggestion.  Into the calculation I entered

                Mod( 27 + WeekOfYearFiscal ((Date(6;27;2011)) ; 2) ; 52)

                In the layout sub-summary I entered the field (which is a number field) - and yes, dates are dates not text. 

                I did a find for date range 07/01/11...12/31/2011, then sorted on that field and every week came back as 1.

                There must be another glitch. It would be great to hear from you.

                If not, Happy Holidays Everyone!


                • 5. Re: WeekOfYearFiscal

                  Reni Smith:

                  Is it possible you entered the static date value "Date(6;27;2011)"?  If so, then you will have the same value across all records.  Insteady, you should specify the date field so you get a different result for each date.  Again, that would be:

                  Mod ( 27 + WeekOfYearFiscal (<date value/field> ; 2 ) ; 52)

                  FileMaker, Inc.