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

# WeekOfYearFiscal

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

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

Reni Smith:

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

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

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.