Week of Year

[sedgely]

I have a query which returns a date field as the week of the year. I have used Format ([Date],"ww&quot this works fine but...
what i want to do is to use the fiscal calendar (1st Week of year starts in April)I have tried using the FirstWeekOfYear paramater to the Format Function but cannot get it to do what i want.
Any Ideas?

Craig

 

[butcher]

DATES eh! i have overcome this way back in the past but i think it went something like this.
subtract 4 months from each date in a calculated field and use this as the basis of any fiscal grouping. hope it sets you in the right direction. How you figger what you'll subtract is the tricky bit. It may come from date difference in days from jan first & April 1st. test for leap years & good luck
THINK: one field to filter & print; one field thinks it is 4 months older/younger/shorter/taller?
Butcher

 

[sedgely]

Thanx for that i will give it some thought and see what happens

Craig

 

[MichaelRed]

Why not just Add or subtract (depending on whether the fiscal year = current year) the "current" week number to (from) the Weeknumber of the start of the fiscal year?

e.g. if the fiscal year starts April 1, then fiscalstartWeek = DatePart("ww", "4/1/02&quot (returns 14)
fiscalcurrweek = DatePart("ww", Date) (returns 33)

so the currFiscalWeek = 19

it could be easily calculated, but you do need to set up the method to identify which fiscal year. assuming that you current fiscal year is 2002, starting on 4/1/2002, you would need a (slightly) different calculation to identify fiscal year 2001 weeks. this part would depend on how yupur company treats the number of weeks in the (fiscal) year, which varies. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[r937]

i've worked with fiscal years before, and the accountants always have weird rules about 4-5-4 quarters and when does fiscal week 53 happen -- because it will

so april 1 often ends up being part of the last week of the previous fiscal year and there's no hard and fast correlation to the week-of-year number that you get from the database algorithm -- just for fun, look up how Access decides when week 1 starts... i think it has something to do with the first thursday in january or something...

anyhow, craig, you may simply need to bite the bullet and create a fiscal calendar table, which you would populate a year in advance and use in all queries that need fiscal data

i've done this for several companies and in the long run it's the easiest solution

here's a hypothetical example --

  caldate     bus  fyr   fwk
  2002-03-27  1.0  2002  52
  2002-03-28  1.0  2002  52
  2002-03-29  1.0  2002  52
  2002-03-30  0.0  2002  52
  2002-03-31  0.0  2003   1
  2002-04-01  1.0  2003   1
  2002-04-02  1.0  2003   1
  2002-04-03  1.0  2003   1

caldate is primary key and is used for joining

bus says whether it's a business day -- weekends aren't, holidays aren't, and the fractional part allows you to record when the company is open only for half a business day (e.g. christmas eve)

fyr and fwk are fiscal year and week numbers

sample query: what day does the fiscal year after the one we're currently in start?

select min(caldate) from calendar
 where fyr > ( select fyr from calendar
                where caldate = date() )[code]

another sample query: assuming a table of daily sales by date, what are the current fiscal year's sales by week?

[code]select fyr, fwk, sum(salesamt)
  from sales inner join calendar
    on salesdate = caldate
 where fyr = ( select fyr from calendar
                where caldate = date() )
group by fyr, fwk[code]


helps?

rudy
[URL unfurl="true"]http://rudy.ca/[/URL]