Aggregate by weeks within an activity date range?

[stevelord]

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.

[Anil_Babu_Samineni]

Steve,

Can you please send that Application / Raw Data?

With out raw data we can't do.

Creating A Master Calendar

[#3]

Hello Steve,

For sure you must start with a Master Calendar. Here is a good article about that: The Fastest Dynamic Calendar Script (Ever)‌. Personally, I use simplified, traditional version mentioned by stephen-x.redmond in THIS comment in above article. Check both and see which one suits you better

Secondly to split data ranges as in your above example, you will need to use IntervalMatch function. Great tutorial here: IntervalMatch

Finally I attach a sample qvw file with implementation of Master Calendar and usage of IntervalMatch function.

I do not understand how are you numbering weeks though. I simply use Week number as per calendar.

I hope it helps.

Final table looks like this:

BR,

Kuba

[stevelord]

Added attachments, thanks!  First tab of the excel worksheet is what I have and second tab is what I need help getting to.  Feel free to drop new script into the qvw and re-attach for me to try.  I have a named license myself and can adapt it to our actual data structure.

[#5]

OK I spot you provided additional explanation when I was writing post. New version of file here:

Screenshot:

[stevelord]

Hi Jakub, this is extremely close - the data structure and dates column are great, but something's off in the activity week column.  On the second challenge it has five days for the first activity week and has a 2-day activity week at the end.  I think maybe because it's using actual calendar weeks as a reference instead of the challengestartdate.  Do you have an angle to make that activityweek column consider the challengestartdate as day 1 of week 1, then run 7-day increments from there?

I think you've gotten it far enough that I might just bang out the activity week column with if statements on the final data table in script.  They'd be the same if statements that were causing accesspoint to time out, but our server could handle them back in script.  If you have an angle more optimal than if statements though, I'd like that.  Thanks!

I will come back to give you correct answer credit and maybe helpful myself or you or whoever irons out the activityweek column.  That dates column got me to 90%.

[#7 · Accepted solution]

Hey Steve,

Try attached

BR,

Kuba

[#8]

Hey stevelord,

Was my last file helpful, or you still require assistance with this problem?

BR,

Kuba

[stevelord]

It's brilliant thank you!  The date range thing and rank ceil / 7 as week stuff.   Sorry for late reply, I've have days off this week, and my top top priorities are actually nprinting automation and three job applicants we're interviewing soon, so a little jammed.  I see your solution works and saved it on my desktop to use as a guide, but might be a couple weeks before I have time to implement it.

Anyway, you're set to be the next gysburt wasserstein I think.  Thanks again!

-Steve