community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #145: SANTALYTICS 2018 - Part 1

Alteryx Partner

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

 

Spoiler
Capture.PNG

 

 

Asteroid
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)

 Santa1.JPG
Highlighted
Meteoroid

My Solution for Part 1

 

Spoiler
Solution.png

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

 

Spoiler
Result.png

 

Bolide
Bolide

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
moreisbetter.png

 

 

 

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

Spoiler
image.png

 

Asteroid

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:

challenge_145_catherineduffy.PNG

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!