3 Replies Latest reply: May 28, 2016 11:34 PM by Sunny Talwar RSS

    Sum last x week at any point of time

    Felix Kiefer

      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!

        • Re: Sum last x week at any point of time
          Sunny Talwar

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

            • Re: Sum last x week at any point of time
              Felix Kiefer

              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.

                • Re: Sum last x week at any point of time
                  Sunny Talwar

                  Does this look like what you were looking to get?

                   

                  Capture.PNG

                   

                  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)