The following calculation returns a 41 not an 11 as I expected.Did I do something wrong? WeekOfYearFiscal(Date;2)
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.
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?
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!
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)
Retrieving data ...