So for each transaction date, you have to determine the end of that week date and the end of week date would be used to determine what week/quarter/month the transaction is counted in. I would start with a calculation that takes the transaction date and converts it to a end of week date and use the end of week date for all your reporting.
Let ( [
F1 = Transaction Date ;
F2 = DayOfWeek ( F1 ) ;
F3 = 7 - F2 ;
F4 = F1 + F3
] ; F4 )
* This assumes you are converting your day of the week to the 7th Day of the week which is Saturday.
Thanks for the quick reply. I was able to have all the dates converted to the end of week date and that seems to be working pretty good for me. What I am struggling with is figuring out how to keep track of the period and quarter that each week falls into because of the start of each fiscal year always being a different date. I am not sure if I should just add a period and quarter field to the sales table and have these handle my calculations. But then I was not sure if I should keep all the date data and calculations in a new table and then use a relationship back to the sales table. I guess I am not sure what is the better practice to use here.
the accounting department here uses 13 periods instead of 12 in a fiscal year. This causes the fiscal year to start on a different date from year to year. But this can change the period that each week will fall under.
Obviously, you need to start by calculating the "period" into which the sale entry falls. Unfortunately, you haven't provided any details about how this works. Are they perhaps using something like this:
Thank you for that link. After reading that and some other pages it dawned on me that my approach was way to complicated. We are using a 52/53 week calendar structure. The year is broken into 13 periods with 4 weeks in each period. Then the quarterly structure is 5 periods in the first quarter then only three periods in the other 3 quarters. I am now just taking the week ending date and calculationg the week number by using the weekofyearfiscal() funciton. That week number is now used in a relationship to a table that holds the weekNo, periodNo and quarterly NO in it. Before I was trying to figure out the period, and quarterly info by using calculations from the day of the year instead of just using the weekNO to figure out how the period and quarter are assigned.
Well, I hope you have worked it out all right, because for me the numbers do not add up.