Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bullish35
Contributor

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
Honored Contributor II

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

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
Contributor

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

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
Honored Contributor II

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

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

HTH
Peter

Community Browser