9 Replies Latest reply: Jul 28, 2016 7:47 PM by Steve Lord RSS

    Aggregate by weeks within an activity date range?

    Steve Lord

      Hi All, I have a data table like this:

       

      ActivityName   ActivityStartDate   ActivityEndDate

      Steps                2/1/2016              2/28/2016

      Exercise           4/13/2016            6/7/2016

       

      (There are actually many of these activities broken out by activityid tied to various clients, but that aggregation should fall into place.)

       

      I want to make a calendar out of it like this:

      ActivityName    ActivityDate    ActivityWeek

      Steps                2/1/2016          1

      Steps                2/2/2016           1

      Steps                2/3/2016            1

      ....

      Exercise          4/11/2016            6

      Exercise         4/12/2016              6

      Exercise         4/13/2016              6

       

      How to do this?  (I have some master calendar script for a single date range that I'm trying to make smarter now, but hoping for an assist if I get stuck. )

       

      Edit: Added a sample qvw and excel worksheet.  Sheet1 of the excel worksheet is what I have.  The second tab shows the result I need.  Thanks!  (Stuff I mention below this point I expect I can work out myself once I have a calendar table.)

       

      -----------------------------------------

      Ultimately, I'm going to join UserActivityLogs onto this.  for instance join the steps log on stepsdate=activitydate and steps.activityname=calendar.activityname, join the exercise log on exercisedate=activitydate and exercise.activityname=calendar.activityname, etc.

       

      Wave 2 will be a bunch of counting how many users participated or met the goals of X number of activities based on days each user logged within the activity date ranges.  I tried just writing if statements  on the front, but it's timing out on accesspoint, so need to aggregate in script and put simpler charts and tables on the front.