How to disassociate activity date from calendar date to calculate number of weeks?

[bullish35]

I am trying to calculate the average number of appointments a particular recruiter has had each week. Currently the appointment (appt date) is tied to the calendar date of my generic calendar.

If I select 2010, the distinct # of calendar weeks equals 52. However, if I then select a recruiter, and that recruiter only has activity during 6 months, then the number of distinct calendar weeks is 26.

How can I disassociate the appt date from the calendar date for the purpose of calculating distinct calendar weeks over any given period? In the attached example, select any year, then select a Recruiter.

Any suggestions would be greatly appreciated!

Thank you,

Ellen

[prieper]

Depends a bit on your definition of a week, but the easiest approach would be to calculate the difference betw MinCalendarDate and MaxCalendarDate, divide it by 7 and round the result up. You may finetune this with WEEKSTART- and WEEKEND-functions.

HTH
Peter

[bullish35]

Thank you Peter, but the problem is that once I select a specific Recruiter the Min/Max dates change to the Min/Max appt_date associated with that Recruiter.

For example, I select the year 2010

• the min/max dates are 1/1/10 and 12/31/10; and,
• the # of weeks is 52

Next, I select recruiter Anthony, Susan. Because appt_date is associated with CalendarDate, the time period changes to the specific min/max dates associated with Susan's appointments within that time period. So,

• the min/max dates change to 3/9/10 and 12/28/10; and,
• the # of weeks, calculated based on these new min/max dates, is 42

What I'd like is for the number of weeks to be based on all of 2010 (i.e., 52) - and that # should be dynamic based only on the selected time period. Without defining a static period of time and hard coding the divisor, I'm not sure that it's possible, but it is the requirement.

Any other ideas?

[prieper]

You may create the calendar as an island-table. But then all formulas must consider this selection (use SET-formulas)

HTH
Peter