Creating a Pivot Table
This is the seventh step of creating your own report with the Reporting module.
In this section, we will extend our analysis using a more complex visualization, Pivot Table. By the end of this section, you will have created a table that shows the monthly spending on flights for the first six months, by department, and by travel class.
- Create a new visualization, as before:
- Select New > Chart on the top menu.
- Choose tutorial_flights again as a data source, then click on the visualization type to get to the visualization menu. Select the Pivot Table visualization (you can filter by entering text in the search box) and then Create a new chart.
- Set the time filters:
- In the Time section, keep the Time Column as Travel Date (this is selected automatically as we only have a one-time column in our dataset).
- Then select Time Grain to be month as having daily data would be too granular to see patterns from.
- Select the Time Range to be the first six months of 2011 by clicking on Last week in the Time Range section, then in Custom select a Start/end of January 1 2011 and 30th June 2011 respectively, by either entering the dates directly or using the calendar widget (by selecting the month name and then the year, you can move more quickly to far away dates).
3. Define your query:
a. Next, within the Query section, remove the default COUNT(*) and add Cost, keeping the default SUM aggregate. Note that Apache Superset will indicate the type of the metric by the symbol on the left-hand column of the list (ABC for string, # for number, a clock face for a time, etc.).
b. In Group by select Time: this will automatically use the Time Column and Time Grain selections we defined in the Time section.
c. Within Columns, select first Department and then Travel Class.
d. All set – let’s click Run Query to see some data!
You should see months in the rows and Department and Travel Class in the columns.
4. Save your chart to your dashboard.
a. To get this in our dashboard, select Save.
b. Name the chart Tutorial Pivot and using Add chart to existing dashboard.
c. Select Tutorial Dashboard, and then finally Save & go to dashboard.