This week is the last week as part of the Data School training. During the whole week we will be given tasks to create 5 different dashboards. Today, the task was to build an Alteryx App that allows the user to pick a date range and category then it should render a Tableau KPI dashboard automatically. The work consisted of two parts: data preparation and automatization in Alteryx and dashboard creation in Tableau.
Data Preparation Stage
I utilized data from data.police.uk API, in order to build a view of local crimes. As first step I had to build an Alteryx workflow:
Part of the task was to unable user to manually select date and category. Therefore I have divided API query into separate parts as some parts of the query need to change, but the query structure must remain specific in order to work properly.
Below are the steps I have undertaken to create the workflow:
- Text input tool: create a new field - ‘url’ with the first part of the url up to the first variable part.
- Add a formula tool creating each of the variable parts with their values as strings.
3. Create a field called ‘query’ which reconstructs the url as a string.
4. Set the URL field, and add the authorization header.
5. JsonParse tool to extract JSON data from Download data field.
6. The data structure is contained in JSON_Name, split to columns on . .
7. Deselect and rename fields.
8. Crosstab using the ID field and columns headers with JSON Value to achieve the desired table format.
9. Autofield to tidy data type.
10. Using the Create Points tool to map out the latitude and longitude data to check that the points are mapping as expected.
11. Use filter tool to select specific category. We will later add interface tool that will allow us to update its value dynamically.
12. Output data into .tde file. Note: If you want to export file with centroid field, .tde is the way to go.
Interface tools
In order to automate our workflow we have to add interface tools.
The first 2 interface tools allows us to select month and year. Drag in a drop down tool , set the List Values to be ‘manually set values’, then enter the numbers 01 to 12 on separate lines. Then we want to ‘update value’. Drill down the Formula hierarchy into Formula Fields, FormulaField[@field=’month’] and select @expression – value=“‘01’”. will allow us to replace the existing value in the month field with the value the user will select. Repeat the same procedure with year.
For the category select we first have to use a summarize tool and group category two times. Then use the cross tab tool to concentrate the columns with category names. Apply dynamic rename to make sure all the underscores are the same.
Finally, connect dynamic rename to the drop down tool and choose update value- operands- operand- value= ' anti-social-behavior'.
Data Visualization Stage
The final step was required to build dashboard using the output file generated in Alteryx. I have then uploaded it to Tableau Public that produced a static look of the dashboard. In order to keep life connection and be able to update the dashboard dynamically, upload it to Server. This way it will continue to refresh and update as the app runs.
Note: To apply changes to the dashboard through analytic app, Tableau should be closed.
Link to the dashboard: Dashboard Week 1 | Tableau Public
