Challenge #55: 2013 Alteryx Grand Prix (L1)

The link to last week’s challenge (exercise #54) is HERE.

2013 Alteryx Grand Prix Lap 1 of 3:

As the Alteryx User groups start the process of selecting their competitors for the 2017 Alteryx Grand Prix I thought it would be fun to bring back an old Grand Prix as the weekly challenge. Note the workflow is in a vertical orientation. Vertical was the only orientation Alteryx had in 2013. Now, we can toggle between vertical and horizontal configurations by clicking on the white space of the canvas, going to the configuration window, and choosing a layout direction.

Want to have even more fun, go to the about box under the help menu and double click the little Colorado flag for an Easter egg that will render the tools the way they looked in 2013.

Have fun solving and remember the competitors were racing each other and the clock! See how fast you can solve this. OH, and there was NO Imputation tool in 2013...

challenge_55_start_file.yxzp

challenge_55_solution.yxzp

Join

Preparation

Advanced

Data Analysis

Intermediate

Transform

Accepted answers

All comments

mceleavey

Okay, I don't think I would win any prizes for speed here, but I think I got there:

Spoiler

I started by determining the year with the most daily sales records. This was achieved by simply creating a date field, grouping by year and counting the individual date rows. I then sorted by the highest and took the first row, then linked this back to the main data stream:

Task 1 - Determine Year.PNG

From here, I then carried out task two by transposing the data to include the values for each department in one column. I then determined the average without zeros by using the appropriate function in the "Summarize" tool and used a formula to replace zero values with this value. I then cross-tabbed the data back to the original format:

Task 2 - Determine Averages without Zeros.PNG

Task 2 - Determine Averages without Zeros.PNG

Tasks 3 and 4 were then simple matters of grouping and applying the appropriate sums coupled with a formula to determine the 364 calculation and daily sales % by department. Simple:

Task 3 - Determine Average values and totals.PNG

Task 3 - Determine Average values and totals.PNG

challenge_55_solution.yxmd

Sales.yxdb

JoeM

The original post has been updated with the solution!

NicoleJohnson

Solution. I do not like you, up and down. I do not like you, Sam I am.

Spoiler

challenge_55_NicoleJohnson.yxmd

estherb47

Nothing earth shattering here, and I did change it to horizontal

challenge_55_EHB_solution.yxmd

Natasha

I successfully completed Pit Stop 1, though it took me ages to do this in a vertical workflow, and OMG that funky icons!! And of course, the suggested solution is much more simpler than mine.

Spoiler

challenge_55_NK.yxmd

LordNeilLord

I have tried and failed several times to get this solution to work, although confident that my calculations were correct it would not validate in the macro.

Spoiler

So I bit the bullet and checked against the solution and found the solution had excluded General Merchandise (not in the instructions) and the format required was short and wide (i was using tall and thin)...so I made all the changes but still no luck. So i resorted to checking the values against the solution and they are all correct....I have no idea why it wont run but hey.....I'm happy with the solution I have provided

@SeanAdams - that's 74! I'm ready for Monday

challenge_55_LNL.yxzp

SeanAdams

@LordNeilLord - you gotta be proud of yourself - you've torn through these with such passion and speed!

patrick_digan

Spoiler

Perhaps it's just me, but i found that to be confusing!

challenge_55_start_file.yxmd

samN

Had Trouble with the Output checker macro so when I checked using the solution realized that the solution omitted General merchandise. I checked myself by taking it out but kept it in when i submitted.

challenge_55.yxmd

ggruccio

I was shocked when the test macro matched my results!

challenge_55_gg_finish.yxzp

LandonG

Solution attached.

challenge_55_start_file.yxzp

LandonG

philipmannering

The macro checker gave me the thumbs up. Solution attached.

Spoiler

challenge_55 - 2013 Alteryx Grand Prix (L1).yxzp

jasperlch

Solution attached.

challenge_55_JL.yxzp

jamielaird

Well vertical workflows are pretty terrible! Solution below.

Spoiler

challenge_55_solution_JL.yxzp

msicak

the old icons are so colourful! my eyes! A nice changeup, going up-down instead of left-right.

took me a while to debug a mistake in the workflow that caused ca 0.05% difference but I got it in the end.

Spoiler

challenge_55_MS.yxmd

dominiklz

Spoiler

first had some issues with the fieldmapping, but that was quickly solved

challenge_55_Dominik.yxmd

JosephSerpis

Challenge Completed

Challenge_55_Joe_Serpis.yxmd

dsmdavid

solved#55.yxzp

daniel_ferreira

Follow my solution file attached...

challenge_55_solved.yxmd

MsBindy

Solution

challenge_55_MsBindy.yxmd

StevenBetts

I got in a bit of a tangle calculating the averages but I got there in the end!

Spoiler

challenge_55_start_file_SMB.yxmd

danilang

Solution

for @NicoleJohnson

I do not like you

up and down.

I do not like. This

flow's a clown.

Spoiler

Dan (No relation to Dr. Dan of the challenge)

challenge_55_solution_DL.yxmd

JDelgado

Grrr... rounded my percentage calculations to two places causing my results to be off by 0.14. Was an exercise in hair pulling before I figured it out (had to reconstruct the macro functionality to find the issue)!!!

challenge_55_my_solution.yxmd

kat

Love the retro challenges!

Spoiler

Couldn't understand why I wasn't getting the same answer until I realised that I was missing a summarise before averaging for stores...

Challenge #55.PNG

Challenge #55.yxzp

OldDogNewTricks

Here is my nasty, brute force method...

challenge_55_completed.yxmd

PrivateStorm

My solution to challenge #55 is attached.

challenge_55_start_file FINISHED.yxmd

OllieClarke

Went well beyond the allowed timeframe, and not only because of the vertical workflow. I also had to open and then save the macro to get it to work, but I got the right answer eventually

Spoiler

challenge_55_OC_solution.yxzp

justynam

My solution :-)

challenge_55_solution_JM.yxmd

JoBen

Cheers!

challenge_55_Joben_Solution.yxzp

MarMu

My first workflow using the vertical layout and I just say. Me no likey. Really poor utilization of the screen and to tend to lose the overview. However, scrolling really works well.

Spoiler

challenge_55_start_file_MarMu.yxmd

Laurap1228

This was a lot of fun! I have to admit, I found the verticle orientation really disorienting.

challenge_55_LP.yxzp

Wouter

My solution:

Spoiler

challenge_55_solution_WK.yxmd

JonathanW

Solved!

Spoiler

challenge_55_start_file_solved.yxmd

stefaniadurdan

Read completely different the scenario in the beginning and I made the workflow more complex, but I hope it helps in case it would have been that way Instead of reading "the year that has the greatest number of daily sales records" I have read "the year that has the greatest AMOUNT of daily sales records. " Probably this is the reason (or at least I hope) why the Pit Stop does not work for me.

Also, I did not manually use the year result for the next questions, but appended the value to the table and use it that way. I would be really happy if you would give me a comment if you check this out.

Spoiler

Instea

StefaniaDchallenge_55.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703