Grouping by specific dates

[Scouter60_RUS]

Looking for ideas on how to approach this challenge.

Preface - I have three years worth of survey data.  The surveys were supposed to be open for only two weeks, however some were and some weren't (over two weeks).  I have  second table with the drop/send dates that vary and do not have any consistency.

Challenge is to use the Send Dates table to alter dates in the Survey Data, if outside any date range change date to max date in the previous date range, if within date range do nothing.

This may be simple but just can't see it right now.

[Luke_C]

Hi @Scouter60_RUS 

Do you have any sample data? Just reading this I would try the following:

1. Filter dates outside of the range
2. Use a summarize tool on the date field to find the max date
3. Use the append fields tool to add that max date to all records

[Scouter60_RUS]

Here is a sample of the two data sets.  Notice there are multiple date ranges, not just one.  The send date plus two weeks makes up each range.

[apathetichell]

O.k. so the key issue you have is you don't know which response ties in with which survey campaign. Without that granular detail you only have the dates surveys were returned and the aggregate number returned. You have the send date and some "two week" period, but honestly - I don't see how to tie that into your returns. Maybe my workflow is off but I aggregated periods for each survey based upon when the next survey was sent. I then join back and look at the last received survey from the prior survey. You get figures like 211 days (see 9/30/2020) return. which is obviously a bit more than 14.

[Scouter60_RUS]

apathetichell,

Yes, you are correct that there is no way to tie them to a set send date.  It is the same survey being sent out at different times.  Sometimes it is sent to the same person if they did not respond to earlier sends.  The error is that they were left open longer than they should have been.  I have much more granularity but nothing that is relevant to the send dates.    The dates with the aggregate are only intended as sample date data.

Send date 1 is 12/27/2019 close date   =>  DateTimeAdd(dt,14,'days') => 1/10/2020

Send date 2 is 1/31/2020 

Send date 3 is 3/3/2020

...

Sample dates from 1/11/2020 thru 1/30/2020 need to be rounded down to the max date of first send.  I could hard code this but would like to do it programmatically based on the send date and the response date, due to the fact their are multiple surveys with multiple varying send dates to be reported on.

This is one operation I am trying to get a handle on.  

Thank you for you insight!

[apathetichell · Accepted solution]

Hi,

check out my workflow - One potential solution is using an arbitrary cut off value (ie two weeks, 20 days, etc) and then looking at the returns in those period vis-a-vis the date outliers. This version uses a running total of surveys received which you can use versus the number of days the survey was received after survey start date. This allows you to see the bulk of the surveys were received in a certain time period (30 days or so) and the remainder make up a tiny subset of your returns.

Hope this helps.

[Scouter60_RUS]

Thank you, this is giving me something to work with.  You get credit for the solution as I was only asking for some inspiration / direction and you have done just that.  I will post my solution when it is done.

Thanks again !!!

[Scouter60_RUS]

@apathetichell 

As promised, this is my final solution.  I wish I could have found out a way to get rid of the bandage formulas but this is what I am going with for now.

Thanks,

Mike