Dashboard week - Day 1

— When we all saw the task this morning.. we were STRESSEDDDDD!—

Today we were tasked with analyzing the data about UK Street Crimes (Link to data)

RULES: I MUST use the API. Build an Alteryx App that allows the user to pick a date range and category and enter a postcode. The app must then render a Tableau KPI dashboard automatically.

Putting it into Alteryx:

This is the API URL I used for Alteryx

https://data.police.uk/api/crimes-street/all-crime?lat=52.629729&lng=-1.131592&date=2017-01

This workflow below is the crux of the macro that will be made

I then wanted to know how many crimes types are there so I know how many categories there are: USING SUMMARIZE TOOL

I then would use this to then make the selecting category toggle - I copied and pasted this into a text input file into a NEW ALTERYX WORKFLOW!!!

NEW ALTERYX WORKFLOW: - Category toggle

The input tool is the category from the previous picture above - I then attached a filter tool because this will help change the drop down toggle and give you the correct values after changing. The drop down interface tool needs to be connected to the lighting on the select tool.

How to update postcode:

Need an account with Mapbox - get a token API from Mapbox

Also another API end points are here!

First the token will be as a text input, then the formula tool will join the token to a postcode of our choosing - I did the dataschool postcode. - Need to connect the token and the mapbox API together (WILL BE SHOWN IN THE NEXT PICTURE). Json pass it - it will give you 4 points like a box, you will only need the center point so we only need to get that out using the filter tool and pivot that.

This is how to combine the token and Mapbox API

The outcome of the transpose should give you this:

This is the center point of the postcode

How to do the date range:

The 2 calendar dates at the top change the start date and end date and provide a range of values of crime that you want to look at

Combine them all together:

Now that all 3 toggles have been set, we need to combine them together and call that API using a batch macro (use a batch macro because of the start and end dates).

The police API that we initially saw at the start has now been edited to change depending on what the user will select and then the API will bring back that specific data.

Outputting it!

Now because the task said we need to upload it to tableau public and it needs to be able to refresh depending on the control parameters of the APP.. I needed to download a new tool called the "Google Sheets Tool"

Need to connect to your google account. Also need to create a google sheets to be able to connect to. This google sheets is what will update each time you update your alteryx app

Connect to Tableau

To connect to tableau - google drive. It will then ask you to sign into your google account. Now the data of the google sheet will work in tableau.

Once I created a quick KPI dashboard, I had to upload it to tableau public.

This was the 20min dashboard I managed to create that has the KPIs of the data - the whole dashboard updates when the user changes the metrics on alteryx

I first saved the data as an extract to then publish onto my tableau public - make sure to save as.

Because you've saved it as a google sheet - on the tableau public, this request data refresh will appear.

Click on this every time you have updated the Alteryx app and then refresh browser. It might take a couple reloads of the browser to show the updated dashboard.

Link to tableau public is here!

Author:

Maha Hussari

View Profile