#SANTALYTICS Part 2

[TaraM]

#SANTALYTICS Continues! Are we having fun yet? Be sure to share your thoughts with your social networks and use the #Santalytics hash tag .

The Elf thanks you all for participating in Part 1. In fact we are so excited over the level of participation, that we are upping the ante. Stay tuned on that.  For now we are onto part 2 and it's going to get tricky. 

With nice kids scattered across the globe, Santa can't be wasting any time this Holiday season! Use the Create Points Tool, of course, to identify where all our presents need to make it this year. We'll have to call on the elves to distribute them to each house, but let's see if we can't keep Santa from making any extra trips.

Determine the least number of trade areas we can distribute bunches of presents to while making sure that no two points in a distribution hub are more than 500 miles apart - remember, we only need to worry about including the nice kids who will be getting presents delivered this year. Once your distribution hubs are assigned, what's the minimum weight that we can use for every one of the hubs while making sure each kid gets a present from the classification of present that they earned? Santa will worry about how many reindeer to hook to the sleigh, but we need to let him know the minimum towage to account for!

Goal of Part 2:

Find a list of delivery "hubs" that include every nice kid - with no two kids in a hub being more than 500 miles apart or 250 miles from the central recipient (hub) location

Identify the minimum weight that be used to deliver presents (with respect to each present class in that hub) to every hub, excluding presents of 0 or null weight

Make sure you are using the data from our Part 1 Solution Blog post!

• Part 1: November 30, 2016: #SANTALYTICS Part 1: Solution and Behind the Data
• Part 2: December 7, 2016: #SANTALYTICS Part 2: Solution and Behind the Data
• Part 3: December 14, 2016: #SANTALYTICS Part 3: Solution and Behind the Data
• Part 4: December 21, 2016:  #SANTALYTICS Part 4: Solution and Series Finale

[SeanAdams]

I've posted a solution that uses a location optimizer macro (in order to get an excuse to learn this).

However - in practice it seems that this is more of a theoretical solution than a practical one for 2 reasons:

a) I couldn't find a way to use the location optimizer tool to calculate the number of nodes (this is an input rather than an output)

b) for 100 nodes with this data set on an 4 processor intel  Core I-7 with 16GB of ram - it took 30 or 40 mins to process.

HOWEVER - I don't want to discard this solution for 3 reasons:

1: Santa was nice last year - so he got a very nice present from Mrs Santa - it was the newest Quantum Computer (which people think can be used to one-day attack the Knapsack problem)

2: Santa is not short on time to run this process - he has all year

3: If we arbitrarily set the number of stops at 400, and look for anything that matches this (based on submissions from the community there is definitely a solution within 400 nodes) - at worst Santa may have a few more houses to stop at meaning he gets more tasty treats at the bottom of the chimney (we always used to leave out Milk and Cookies for Santa)

So - for any of the elves - rather use one of the solutions posted by one of the other folk unless you too are trying to learn the Location Optimizer macro...

[SeanAdams]

:-) so I got laughed out of the room by the elves.

"But Santa is using the Quantum Computer already to figure out new cheat codes for Super Mario Bros - so you'll have to come up with something else" 

So - had to put together an iterative macro to find hubs.   Came up with 349 hubs, and it ran for 8 mins.    I've just attached the macro, and the macro tester

Spoiler

- Cross-joined all recipients to all other recipients
- Filtered out any distances over 250km
- Then sorted this to find the recipients who had the most rows remaining (i.e. the recipients that have the most people living within 250km of themselves).   Call his HubA
- Then scan down the list to find any other hubs that are more than 500 miles from Hub B
- Then filter out any recipients that are not served by Hub A or Hub B
- Then send these recipients back through the flow.