Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bullish35
Creator II
Creator II

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

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

3 Replies
prieper
Master II
Master II

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
Creator II
Creator II
Author

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
Master II
Master II

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

HTH
Peter