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

    WeekOfYearFiscal

    brian23dennis

      Summary

      WeekOfYearFiscal

      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
          davidhead
             It would be helpful to know what date you are using!
          • 2. Re: WeekOfYearFiscal
            ReniSmith

            Hi,

            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
              TSGal

              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?

              TSGal
              FileMaker, Inc.

              • 4. Re: WeekOfYearFiscal
                ReniSmith

                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!

                R

                • 5. Re: WeekOfYearFiscal
                  TSGal

                  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)

                  TSGal
                  FileMaker, Inc.