Pivot
Pivot Table makes it super easy to not only summarize (aggregate) data but also spot outliers or patterns quickly by using color. And, just like any other visualization (chart) types you can share it with reproducible data preparation steps simply by clicking a button and start having a conversation around the data.
Row - Assign a column you want to show at Row Headers. If it's a Date / Time column assigned, you can select the aggregation level such as
Month,Week,Day, etc. You can assign multiple columns to Row.Value - Assign a column you want to show at the value area. If it is a numeric column assigned, you can set the aggregation function such as
Sum,Mean (Average), etc. to aggregate the values. If it is non-numeric column, you can set the count based aggregation function such asCount (number of rows),Number of Unique Values, etc. You can assign multiple columns to Value. You can also assign a Window Calculation for each Value from the gear icon on each Value. Look at the Percent (%) of Total section below for detail.Column - Assign a column you want to show at Column Headers. If it's a Date / Time column assigned, you can select the aggregation level such as
Month,Week,Day, etc.Sort - Assign a column to use for Sorting the Row values. The default is based on either the alphabetic order or the factor level order for Factor type columns.
You can use "Color By" to format the Value cells with colors based on the Values, which would make it easier to spot the patterns or trends in the data. The formatting is based on the value range of each target group. Basically what it does is, it figures out the value range based on min and max values, splits the range into 5 equal length, and applies the color palettes. Following target group options are available.
It formats Value cells for each row.
It formats Value cells for each row group, which means the 2nd Row Header group from the right.
It formats Value cells for each column.
It formats Value cells for the entire pivot table.
You can change the color palette and reverse the order of the color assignment by clicking a gear icon.
If you assign multiple columns to Value, the Color By options applies to all the columns.
If you assign the "Percent of Total" window function on any Value column, the Color By option will be available only for the direction selected at the "Percent of Total". See the "Percent of Total" section following for details.
The "Percent of Total" calculates the ratio of values for each group (direction). You can assign "Percent of Total" by clicking the gear icon on each column assigned to Value, selecting "Window Function" menu item and choosing it from the "Calculation Type".
Following direction options are available.
The ratio calculation is done for each row.
The ratio calculation is done for each row group, which means the 2nd Row Header group from the right.
The ratio calculation is done for each column.
The ratio calculation is done for the entire pivot table.
If you assign multiple columns to Value, the Color By options applies to all the columns assigned.
If you assign the "Percent of Total" window function on any Value column, the Color By option will be available only for the direction selected at the "Percent of Total".
You can sort by the column by clicking the column header. Note that the sort happens only for the rows fetched.
You can categorize numeric values inside the chart. See Category(Binning) for the detail.
You can use the Custom Function feature to define your own aggregation function at Value. See Custom Function for the detail.
You can set a URL Link on each measure column. You can dynamically construct the link with the associated column values by referencing the column name by ${}. Once you define a URL Link, the column value will be clickable and if a user clicks the value, it opens up the URL in an external browser.
For example, if you have a "Country" column in your Pivot, you can set the URL http://exploratory.io/?q=${Country} like following.
Then, the Pivot will look like this.
If you click "Brazil", a browser opens up the URL https://exploratory.io/?q=Brazil
Take a look at Layout Configuration on how to configure the layout and format.
You can check out the following articles for more information.
​Introducing Pivot Table​
