In our second Amazon QuickSight introduction blog post we will go through the basic steps in building a dashboard. These steps include importing a CSV data set, creating calculated fields and data analysis. The data being analyzed is in regards to non–local employee travel which is a public access data set and the source of the data is data.gov.
We utilized the data set of employee travel data (non-local) of Montgomery County of Maryland. This data set gives insights into the total approved actual expense incurred by Montgomery County Government employees traveling non-locally. The data set comprises of 7 columns and 1412 rows.
This data set includes the following columns: Department, Employee, Travel Start Date, Travel End date, Destinations, Purpose of travel, and Actual total expenses.
Our dashboard will enable the user to analyze how travel expenses are spread across each department. Also, the user will be able to select top 5 employees that that has the highest travel expenses, the top 5 reasons for what which they travel for and the top 5 destinations that they travel to.
Creating a basic dashboard
We start by importing the data as a CSV file.
Once the source is selected and uploaded, Amazon QuickSight will redirect you to the analysis page. Here you can see the fields (Columns) of the data set and the available visual types.
The first step is to understand how much money Montgomery County of Maryland has spent on travel expenses. Also, we want to figure out the departments that spent the most and least amount of money. For that we create a data card to display the total expenses by all the department and two more data cards to display the department that spent the most and the department that spend the least:
Now we will create a horizontal Bar chart to visualize the top 5 most expense departments. This involves using the Top N Filter and here is how you do it.
Click on the horizontal bar chart under visualizations and Drag the department field to the Y-axis and actual expenses measure to the value field. On the left pane click on the filter icon – Select department and set filter type as Top and Bottom filter and enter ‘5’ under “Show top” filed and choose ‘Actual Total Expense’ under ‘filter by’.
Now that we can see that Police department spends the most, the next step is to see the top 5 employees who incur the maximum travel expenses and for that, we create similar charts to visualize top N expenses by employees by the purpose of travel. In addition to creating filters Amazon quick sight lets you create calculated fields as well. The below screenshot shows the different built-in functions that Amazon quick sight provides to create calculated fields:
A basic dashboard that shows the total expenses and top 5 expenses by department and by the purpose of travel looks like this:
Coming up next
For my next post, I’ll explore the data modeling capabilities of Amazon QuickSight.
