interpolating multiple date ranges

[sarmstrong]

Hello,
I'm trying to join 2 datasets on date ranges plus 2 other fields. I've figured out that the way to join on dates is to make duplicate date fields formatted as a strings and then discard the duplicates.

The problem I'm trying to solve is that one data source has date ranges in week-over-week format, while the other has date ranges corresponding to the total length that campaigns ran. 

For example, data source A might have:
Brand X, Nov 1 - Nov 8, 30 clicks

Whereas, data source B would have, for the same brand, 250 clicks over the life of the campaign (say, Oct 25 - Dec 1). 

I'm thinking a brute force method of solving this problem would be to iterpolate average daily values for all of the date ranges in data source B, and then use the date filter 52 times, once for each week of the year.

Hoping someone has a better idea.

 

[Ned · Accepted solution]

1st - if you ever find yourself want to add 52 filter tools, you are right, there is almost always a better way...

In both cases you can use a GenerateRows tool to generate all the days in the range.  That way it won't matter if it is report by week or by campaign or any other arbitray date range.  Once you've generated a record for each day, then figure out how many days in the range to and divide the clicks to get clicks per day.

Once you have that, you can sum on any interval you want.  Maybe monthly.  Maybe by week.  Using the DateTimeFormat function to make a string for grouping on.  %W will get you the week # of the year.

I did a quick module with your above data - you can download it here:

https://www.dropbox.com/s/e6472ywipb1l6h1/DateTimeGrouping.yxmd?dl=1

[Jesse_Bernard]

Ned,

You may have started answering the question that I am about to pose.  I currenlty generate rows to create an interval date for the different segments of the process, but I eventually want to organize the output by month.  That works fine when a project date starts or ends on the beginning or end of a month but I am having harder trouble working with the information associated for dates in between.  Right now I am able to generate rows on an interval of 1 month, but that is based on the project begin date and the stages throughout.  I now want to take that data and make sure that I have an interval date at the beginning and end of each month.  Example below:

Project Starts : 04/15/2016

Stage 1: 4/23/2016

Stage 2: 5/3/2016

Interval Date: 5/15/2016

Interval Date: 6/15/2016

Project End: 6/18/2016

I can determine the number of days in between each of those and determine the manhours associated but when I go to organize by month, the manhours span at least two months in some cases.  I have the above example working. 

What I need is:

Project Starts : 04/15/2016

Stage 1: 4/23/2016

Interval Date: 4/30/2016

Interval Date: 5/1/2016

Stage 2: 5/3/2016

Interval Date: 5/31/2016

Interval Date: 6/1/2016

Project End: 6/18/2016

Thoughts?