Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Do you have a sample you can share with the expected output to help you better?
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.
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)