Sum last x week at any point of time

[#1]

Hi all!

I am trying to create a line chart that shows the percentage of the people a specific user has seen in the last 6 weeks at any point in time.  So for example: User A has 100 people assigned and I wanna see how many of him he has seen in the last 6 weeks at any day I select (dynamic trellis?!)

The part I am stuck is that I my expression shows me the percentage per date and not the last 6 weeks. I tried if statements and set analysis in lots of different ways and I just can't make it work. Here the expression iuse at the moment:

count(distinct {$<UserGroup={'1'},ActivityType={'Appt'}>}  ContactID)

/

sum(Assigned_Contacts)

My dimensions are:

1. if(Week>=weekstart('$(vMaxDate)'),Week)     //shows the last 6 weeks. the requirement is to show the last 6 weeks, but look back 6 weeks at any of the week shown in the graph. So if we see the calendar week 10- 16, week 10 would have to show the value of week 4-10

2. User ID

I hope that makes kind of sense. If you need more information, let me know.

Any help is much appreciated. Thanks guys!

[sunny_talwar]

Do you have a sample you can share with the expected output to help you better?

[#3]

Thanks for your reply! Much appreciated.

I have worked on the problem and made it at least kind of work. Attached is an sample, I tried to comment as much as possible in the short time. Hope that helps you to understand what I did, what i am doing and where I try to go to.

6week trellis.qvf - Google Drive

Let me know if you have further questions.

[sunny_talwar]

Does this look like what you were looking to get?

Expression:

Only({<ACTIVITY_DATE = {"$(='>=' & TimeStamp(Max(ACTIVITY_DATE)-70, 'M/D/YYYY hh:mm:ss TT') & '<=' & TimeStamp(Max(ACTIVITY_DATE), 'M/D/YYYY hh:mm:ss TT'))"}>}Aggr(RangeSum(Above(

//Value to sum

(Count(Distinct {$<Code={'GP'}, ACTIVITY_TYPE={'CR_A_APPT'},

ACTIVITY_DATE = {"$(='>=' & TimeStamp(Max(ACTIVITY_DATE)-70, 'M/D/YYYY hh:mm:ss TT') & '<=' & TimeStamp(Max(ACTIVITY_DATE), 'M/D/YYYY hh:mm:ss TT'))"}>} CONTACT_ID)) 

//offset of above than calculation of the range to sum (last 6 weeks)

,0,6)), (USER_ID, (TEXT)), (Weekstart, (NUMERIC))))

/

sum({<ACTIVITY_DATE = {"$(='>=' & TimeStamp(Max(ACTIVITY_DATE)-70, 'M/D/YYYY hh:mm:ss TT') & '<=' & TimeStamp(Max(ACTIVITY_DATE), 'M/D/YYYY hh:mm:ss TT'))"}>}Assigned_Contacts)