cancel
Showing results for
Did you mean:

# Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!
New content is available in Academy! You may need to clear your browser cache for an optimal viewing experience

## Challenge #145: SANTALYTICS 2018 - Part 1

Alteryx Certified Partner

Only difficulty for me was to read the files, but nice challenge!

Spoiler

Fireball
Spoiler
I've gone with "The Denver Health and Hospitals Foundation", like others there seems to be a bit of confusion over the fundraising efficiency calculation. In the documentation it states it is calculated as

(When Calculating Using Form 990) = Average of Part IX line 25D ÷ Average of Part VIII line 1h

I couldn't find a line 25D in the data, so took what I considered the closest one which is "Total functional expenses" which appears in Part IX line 25(A)

Meteor

My Solution for Part 1

Spoiler
Atom

I got fed up with the ridiculous column names so I put a dynamic rename in :)

Spoiler

Quasar

I opted for the "Why use less tools, when you can use more?" route!

Also found that "A Precious Child" is the way to go and in Arizona, it's the Yuma Community Food Bank!

Spoiler

Alteryx Partner

that was fun...now to attempt part 2!

Spoiler

Quasar

Had to work on live workflows, so just was able to work on this.  Extra credit included, so this is really designed as an app that allows for selection of state.

Asteroid

My solution:

Asteroid

Solution attached.

Asteroid
Spoiler
Step 1: read and parse all files (17eofinextract990 and pub78), save to .yxdb for faster access.
Step 2: determine agencies that fit criteria specified.
Step 3: read and parse 2 previous year files (16eofinextract990 and 15eofinextract990), save to yxdb for faster access.
Step 4: determine averages of lessdirfndrang and totcntrbgfts for all agencies in step 2 over last 3 years ('15, '16, '17).
Step 5: calculate efficiencies for all agencies: avg_lessdirfndrang/avg_totcntrbgfts.
Step 6: sort efficiencies in ascending order for top agency.

Enjoy!