Exploring data with Pivot Tables
On this page you'll learn how to explore and visualize your data in real-time, discover trends and get valuable insights, with no technical background needed, using SlicingDice's Pivot Table Tool from the Data Exploration & Visualization Module.
Creating a custom Pivot Table
On SlicingDice you can use the Pivot Table tool to explore your data. To create it, on the SlicingDice's control panel access the Pivot Table Tool inside the Data Exploration & Visualization Module.
Now you can choose what data and aggregations you want to explore by filling the options shown:
- Data Source: All of your databases and its dimensions. The items are in the
- Rows: The dimension results will appear in rows and the values will appear at the right side
- Columns: The dimension results will appear in columns and the values will appear at the right side
- Values: Operations of dimensions. You can select the sum, the average , the count, the minimum and the maximum values of a dimension.
- Pivot Chart: You can visualize the data of your Pivot Table in a chart. The default value is (none)
Now click on Apply to finish the Pivot Table creation and start analyzing the data selected.
Pivot Table options
You can select what rows/columns to show by adding keywords on the Filter field. This can be done at any time inside the Pivot Table Tool, on the upper right corner of the page.
List of accepted filtering operators, with examples
John: Search for exact match if possible, otherwise will search all results that contain "John"
product*: Search all matches that start with 'product'
*star: Search all matches that ends with 'start'
year:2015: Filter by '2015' is applied to dimension with "year" keywords in the title
-2015: Exclude all keys that match "2015"
year: 2015, year: 2016: Filter by year=2015 OR year=2016
user:"John Smith": Search for exact match on "user"
year:2015+quarter:1: Filter by year=2015 AND quarter=1
>=20: Filter measure values that are greater or equal than 20
amount>=20: Filter measure values with "amount" keyword in the title that are greater or equal than 20
-"": Exclude empty keys (displayed as "(empty)")
You can sort the data presented on your Pivot Table. This can be achieved in two ways:
- By labels:ordered based on the values of the columns that are selected as "Rows" or "Columns".
To change the order direction, click on the appropriate header.
←indicates A-Z order (ascending)
→indicates Z-A order (descending)
- By values: ordered by the table row/column values. To sort by concrete row or column values,
click on the last-level label (or measure name, if present).
It is possible to keep A-Z order of groups, and order by values only last-level labels with the help of the Sort by value only inside group checkbox on the Format tab.
The drill-down allows you to get a more detailed view of the results, enabling a deeper analysis on a specific matter. To drill-down on your Pivot Table, click on any cell with a value. A menu will be presented, with two options:
- Show rows: this option will open a new "flat table" report configured to display records of the selected group
- Drill-down by dimension: you can choose which dimension to use to get a more detailed view of the group. As result, this dimension will be added to rows (or columns) and Filter will be changed to limit the pivot table by the rows/columns that correspond to the group
You can add charts with your selected data from the Pivot Table for a more insightful data exploration operation. Select a chart type on the Pivot Chart menu. The possible charts types at this moment are: Bar, Stacked Bar, Line, Scatter, Area and Pie/Donut.
Pivot Table Tool charts
The charts mentioned above are created from your Pivot Table data, and should be used to help you exploring the data and identifying patterns and trends on the fly. For more elaborated charts, with plenty of options and interactions, it is recommended to use the Reporting & Dashboards Tool, also inside the Data Exploration & Visualization Module.
Pivot Table formatting
The Pivot Table formatting can be very helpful to ease the deep understanding of your data. This feature can be accessed on the Format tab, with options to limit and configure your Pivot Table. The functions available are:
- Value: The format of the value to be shown in each cell. The default value is Number, but you can select from % of grand total, diff from prev column, running total by column and more
- Heatmap: This dropdown box allows you to highlight values based on a given category. In the heatmap, higher values are highlighted with red
- Row and Columns limit: The first dropdown limits how many rows can be returned and the second dropdown limits how many columns can be returned
- Preserve labels grouping: By selecting the checkbox, it keeps the order of groups by label (A-Z or Z-A), and by values order is used only for last-level rows (or columns)
- Totals: Select if your pivot table will show results with totals for rows, columns, and/or an overall total
- Subtotals: Select if your columns/rows will have their own totals
You can export and share your reports done inside the Pivot Table Tool whenever convenient.
To do it, click on the Export button on the upper right corner (next to the Filter field) and select your export option: PDF, CSV, Excel, Excel PivotTable or JSON. After selecting an option, a download of the file format chosen will begin.
Your downloaded file will contain all the filters that were applied when you clicked the Export button.