Calculation for defining a date
I'm going insane trying to get this calculation to work. Would love some help.
I'm tracking weekly sales for various companies. The year is divided into 4 quarters.
There is a Company Records table with a primary key and a WeeklySales table matching up to the company records key. There is also a global year and a global quarter field in the company records table. In the weekly sales table there is a year field, quarter field, and a "week of quarter" field (ie, the numbers 1-13 depending on the week in the quarter). The fields that match from the two tables are the primary key for the company, the year and the quarter fields.
On the main Company Records layout, the user selects the year for the global year field from a drop down list, then a quarter for the global quarter field from the drop down list, then enters the sales data into a portal to the weekly sales data with one field for each 13 weeks of the quarter.
This then creates a record in the WeeklySales table with the year selected, the quarter selected, and the week number the data was entered into.
What I need is for there to be another "date" field in the weekly sales table which enters the actual date for this particular record based on the year, the quarter and the week of the quarter.
A complication is that for this scenario, Q1 actually is the final quarter of a year. So Q2 is the first week of the year (starting on a Sunday), Q3 starts on the 14th week of the year, Q4 on the 27th week of the year and Q1 on the 40th week of the year.
So far I've tried creating an "ActualWeekofYear" field, which tries to calculate the number of the week of the year (so Q2, Week 1 = 1 but Q3 Week 1 = 14), and then I would get another calculation to work out the date from that.
But I can't get that first one to work yet.
These are the calculations I've tried so far, and so far I'm just getting zero entered into the "ActualWeekofYear" field.
QuarterNEWSTRUCTURE = "Q1" ; WeekOfYear = var4 ))
Can anyone see where I'm going wrong? Am I over-complicating it?
Thanks for any help.