This week is dashboard week for DS5, which means that every day we have to make one Alteryx workflow and one Tableau dashboard, write one blog post about it, and then present it the next morning.
Everyone is welcome to participate, and if you are interested in joining us then check out this post where Andy writes about our assignments every morning. By the way, we also learn about our task for the day from that post 🙂
Today’s task
Make an Alteryx app that will query the UK police API and then open a Tableau dashboard pre-populated with the crime data. User must be able to select a specific crime type and a date range. Since the API returns crimes within 1 mile of a chosen location, users must also be able to choose a location by entering a postcode.
Alteryx workflow
At the beginning of our training we already learned how to work with APIs, and a few weeks ago we had an Alteryx week where we did our first macros and app. Hence the task looked pretty straightforward at first, and only later on did all the small details start to appear.
My workflow challenges
- The API requires the latitude and longitude as part of the query string. This means that we first need to look up the latitude and longitude for each postcode before supplying these as part of the API request.
- The crime category in this particular API doesn’t follow the pattern of a regular parameter, which would look something like this: http://……&category=robbery. Instead it is part of the main URL link, followed by a question mark: https://…./robbery?  This means that the crime category needs to be specified within the URL string and not as a separate query parameter that can be used with the standard payload functionality of the download tool.
- The API request supports the call only one month at a time, which means that if the user can select a date range, then multiple API requests need to be made in order to download all the data. In order to do this, a batch macro is required. For the batch macro to function properly, complete URLs for each month need to be generated through the tool called Generate Rows.
Things to be mindful about
- Start date should be less than or equal to the end month
- API data is aggregated at the month level, so we need to disregard the day value from the date selection interface
- Postcodes could be written in upper or lower case, with or without spaces
Things that can be improved if there would be more time available
- Implement the warning that so far the latest month available is January 2017, which is a rolling date moving forward
- Implement postcode validation
- Implement the drop-down selection of crime categories by only relevant categories for the dates selected, since there were a few minor changes to the category list in 2013.
Here are the screenshots of my Alteryx app and batch macro
Tableau dashboard
Since a lot of time was spent on data prep, the Tableau dashboard had to be done quickly. There were not many dimensions and measures to visualise, so I decide to go for a map with some KPIs.
My dashboard Challenge:
- Stop using RED on black/white on dashboards.
When I heard that we are going to visualise crime data, I immediately remembered the application viz created by Wiktoria about crimes in Hell’s Kitchen. I really liked it, and was inspired by it, so I started creating something similar.
When I asked my fellow DSers for feedback, Laura pointed out that red dashboards are ‘my thing’. Indeed, my Tableau Public profile features a few red on black and red on white dashboards. Realising that Laura was right, I decided to rethink the colours, and here is how my final dashboard looks like.
My blog post challenges
- Keep it short
- Don’t overthink it
Day 1 Summary
I enjoyed the challenge of implementing the API app and I am also grateful for feedback about my colour choices, so that I will be more mindful about that from now on. Still, a blog post is the biggest challenge for me, being a non-native English speaker. I am looking forward to Day 2 for more learning and practice.
