Can anyone think of another way to do this without having to create 91 relationships?
Yes, of course - even two ways:
1. Use records instead of fields, i.e. create 91 records in the Days table (actually, it should be 92 records - since a quarter can have up to 92 days).
2. If this is for display only, you can use a repeating calculation field - with each repetition checking its date against a list of promotion dates.
Thanks Comment. Yes, it's for display only.
I haven't worked with repeating fields yet. Would I set the first repetition to be day 1, and then set the following repetitions to be incremented by 1? How do you do that? (I will of course go and do some research.)
I should add, I plan for the repeating fields not to have dates, but day numbers. I have two global fields, one with the year and one with the quarter, and with those pieces of information I can cross reference against the promotion dates.
These global fields are used to select the particular sales data you see. So having said that, I guess that means the day repeating field needs to be in the main table (where the global fields are), so they can be part of the same relationship.
It depends on how you want to select the quarter, but basically the calculation would look something like =
firstDayofQuarter + Get ( CalculationRepetitionNumber ) - 1
with the result type set to Date.
I have two global fields, one with the year and one with the quarter
Then try =
Date ( 3 * ( Extend ( gQuarter ) - 1 ) + 1 ; 0 ; Extend ( gYear ) ) + Get ( CalculationRepetitionNumber )
I should add, I plan for the repeating fields not to have dates, but day numbers.
Not a good plan, IMHO: you need dates to compare to dates. But you can format the field to display only the day element of the date.
"Not a good plan, IMHO: you need dates to compare to dates."
Is that still the case if in my promotion table, I have the dates being converted to days of the year? Then the relationship is set up to match up the following:
Day of Quarter
Why not use one field - date - that contains all of this information, instead of splitting it up into three? For the relationship, you could use:
QuarterStart ≤ PromotionDate
QuarterEnd ≥ PromotionDate
Or, if you're going with the repeating field, you can simply use that as the matchfield against the promotion date.
The main reason is that all the sales data relationships are all based on the Company ID, Year, Quarter and Week of Quarter (rather than the date). To go back and change all those relationships would be a lot of work.
Perhaps it wouldn't be necessary to change them all - I'll have to have a look at it again in more detail.
To go back and change all those relationships would be a lot of work.
Perhaps - but with a lot of satisfaction, too...
Ok - I've changed over the repeating fields to the dates. You're right - it makes it less complicated, except for the fact that the financial years and quarters are a bit funny!
Weeks 1-13 of 2009 = Q2, 2009, weeks 1-13
Weeks 14-26 of 2009 = Q3, 2009, weeks 1-13
Weeks 27-39 of 2009 = Q4, 2009, weeks 1-13
Weeks 40-52 of 2009 = Q1, 2010, weeks 1-13
I'll try to rejig the calc you gave me, but if you happen to be able to see quickly how to do it, that would be fantastic!
Thanks for your help.
Your quarters are based on weeks?! When exactly does each quarter start (given that weeks run independently of months/years)?
The people entering the sales data had been thinking of it in terms of the week of the quarter, and so I had them entering it in via a relationship that set the year, quarter and week of quarter depending on which field they entered the sales into.
Then I had a calculation working out the start date of that week:
If ( Quarter = "Q1" ; cWeekOfYear * 7 + Date(1; 1; Year -1) - DayOfWeek(Date(1; 1; Year -1))-6 ; cWeekOfYear * 7 + Date(1; 1; Year ) - DayOfWeek(Date(1; 1; Year ))-6 )
It seemed to be working, but perhaps I'm going to run into problems.
This structure was set up a while ago, well before my very steep learning curve!
By the way, I got the repeating field dates to work by setting a global start date field when the user clicks the quarter forward (the start date is set based on a related table with the beginning dates of each quarter having been manually entered), and then the repeating field calc is:
Extend (rDatesStart) + (Get(CalculationRepetitionNumber)) - 1
I am afraid that doesn't answer my question: I am looking for the logic behind this, not a specific calculation; a logic that would enable me to determine the quarter of any given date (in your calculation, the quarter is already known).
Specifically, consider the case of a year starting on a Saturday. If you go by the WeekOfYear() function, then Jan 2 is already Week 2, and the entire quarter contains 1 + 12*7 = 85 days. The following 2 quarters will have exactly 13*7 = 91 days each, so the last quarter of the year (it doesn't matter if you call it the first quarter of the next year) ends up with the remaining 98 or 99 days.
There's nothing wrong with that, if that's how the business wants to divide up the year - but we need to be clear about the rules before we can move any further.
Sorry about the delayed response.
I can answer your question better now. The dates of each quarter is determined by a separate Fiscal Year table, which has fields Fiscal Year, Quarter, date start, date end.
Then in the promotion table, the user puts in the date fields and there is an auto enter calculation field on the fiscal year and quarter field in the promotion table, which draws the info from the Fiscal Year table.
I've changed over the relationships as you suggested to be based on the dates. This is all working well.
I have the repeating fields set up with conditional formatting, and the appropriate repeating fields get highlighted if there is a promotion matching the dates in the repeating field.
However, this only works with one promotion per quarter. If I put two different promotions in the same quarter, the conditional formatting works only to highlight the first promotion entered in those date ranges.
Do you have any ideas as to how to be able to represent multiple occurences of the promotions?