I had a calculation relating to my AFL Data Viz that I had been avoiding doing because I assumed it was too difficult. Over the weekend, with the help of Alteryx I was able to easily solve the problem and it turned out to be much easier than I had thought. The key for me, was breaking down the calculation into small parts. What I wanted to achieve was to calculate the percentage of games where the winning team has more kicks (or any other metric) than their opponent in the AFL. My first step was to filter the data right down to 1 team, over 3 weeks and do the calculation myself on paper.
Looking at my dataset, the main issue that I saw was the shape of the data. It was 46,000 rows long, with a row per player/game:
Where I wanted to get to, was a calculation on Teams, still including if they had won or not and adding how many kicks their opponent had scored. So, something like this, but with metrics totaled by team:
I achieved this with the Alteryx workflow below:
The key steps that I hadn’t been able to do previously turned out to be just the summing and the join. Summing, gave me the game totals that I needed, and joining them with the oppositions row in that game gave me the other columns! In hindsight, this could also be done in a database query, or in Excel or with code, however I like that Alteryx does it visually with very granular info for troubleshooting.
The output I got was in a much more manageable format:
I imported the dataset into Tableau and started trying to do the calculations that I wanted (Total wins with more goals ÷ Total Games). It wasn’t so easy though and again I thought, wouldn’t this be easier if I broke the problem down into smaller parts. So I went back to Alteryx and produced this:
It quickly gave me the percentages that I needed for each key metric. A bonus was that these 2 new datasets gave me much better visibility of the totals for further calculations. So I thought I’d better put a few in a viz here:
https://public.tableau.com/profile/blake.stroh#!/vizhome/AFLViz-Part2/DeeperDive
Please let me know if you have any feedback? A better way of doing it? I know this isn’t the perfect solution, but for my case it got me to where I wanted quickly, in a way that I was comfortable with. I feel like this ‘divide and conquer’ approach, will come in handy for some more complicated calculations down the track.