• Creating a Pivot Table
• Applying Functions on Columns
  • Advanced Summarizing Options
• Applying Filters
• Providing User Filters

A Pivot Table allows you to dynamically summarize large amounts of data for easy analysis and visualization. You can transform data in a table(s) into meaningful summaries easily by using the intuitive drag and drop interface provided by Zoho Analytics. With Pivot tables you can easily aggregate and filter the values the way you want it. Please note that only Server Administrators and Managers can create new pivot tables. To know more about the user roles in Zoho Analytics, click here.  

 Creating a Pivot Table

To create a Pivot View, follow the steps given below:

• Open the Workspace containing the required data with which you want to create a Pivot Table.
• Click New Report button in the top left corner to open Create New Report window.
• In Create New Report window, click Pivot View (Matrix View). The Select Base Table dialog box opens as shown below.

• Use Select Base Table dialog box to choose the required table in your database on which you want to create the Pivot Table and then click OK.
• This opens a window in Edit Design mode as shown below:

You can also create new Pivot table by opening the corresponding table on which you want to create the pivot table and invoking the New - > New Pivot View option in the toolbar. In Edit Design mode, you will notice that all the columns of the selected table listed as drag and drop items on the left side Column List pane. On the right hand side, you can see the Design Area with shelves to drop the columns and preview area below to view the Pivot created.

• Drag and drop the required columns listed in the Column list pane into the respective shelves in the Pivot tab. You can also select the check box adjacent to each column listed to auto place the columns into the appropriate shelves. After dropping the columns click on the option Click here to Generate Pivot to create the new Pivot Table.

Once you have created the required Pivot Table, click Save button in the toolbar to save the view. In the Save As dialog box that appears, type the Name, Description for the Pivot Table and choose the folder where the view is to be saved and click OK.

Below is a brief description of each of the shelves in the Pivot tab.

• Column: Distinct data values present in the columns dropped in this shelf will appear as the column headers of your pivot table. You can also have multiple columns dropped in this shelf for summarization. When you add multiple columns, they will be displayed as categorized layers (group within group) of data vertically.
• Row: Distinct data values present in the columns dropped in this shelf will appear as the row headers of your pivot table. You can also have multiple columns dropped in this shelf for summarization. When you add multiple columns, they will be displayed as categorized layers (group within group) of data horizontally.
• Data: Values of the columns dropped in this shelf will appear in the body of the pivot table. Values will be summarized based on the summary function that you select. Also it will be categorized to provide the appropriate summary value for each cell corresponding to the row and column value in the table. You can also have multiple columns dropped in the Data shelf for summarization. When you add multiple columns to the data shelf, you can choose to display the values either as rows or columns.
  
  

The best way to understand how to summarize data in a Pivot table is by example. The following is a table showing sales data of a store.

In the example shown below, pivot table summarizes the data in the above table and presents the total sales in each region grouped by each year. In this example, the pivot table has Date column in Column shelf, Region column in Row shelf, and Sales column in Data shelf. Sales in each region is displayed using a Sum aggregation. Also, Zoho Analytics automatically summarizes (in this example it is addition) the values of each row and column based on the summary function that you select. Summary row and column will be added at the bottom and to the right of the pivot table.
Functions that are applied on each of the column in the example here are:

• Date - Year (all distinct years in the column will be displayed)
• Region - Actual (all distinct values in the column will be displayed)
• Sales - Sum (sum of the values in the column will be displayed)

You can also change a data column to use any one of the other functions like: Sum, Count, Average, Standard Deviation etc., depending on the data type of that column, which will be discussed in the next section.

Multiple columns in Row, Column and Data Shelves

• Columns/Rows shelf
• Data shelf
  • Data as column
  • Data as row

Columns/Rows shelf

Zoho Analytics allows you to drop multiple columns (up to 15 columns) on each shelf. When you have more than one column in the Column or Row shelf, Zoho Analytics automatically groups data in the order they appear from left to right or from top to bottom.

For example, if you have Region and Product Category columns in the Rows shelf, all Product Categories will be grouped for each Region. Similarly, you have Date as Year and Date as Quarter in the Columns shelf, then all 4 Quarters are grouped for each distinct year. Zoho Analytics also provides the corresponding summary information.

Data shelf

Zoho Analytics allows you to add one or more columns to the Data shelf. On adding multiple columns, Zoho Analytics enables you to display the data in one of the following formats: Data as Column and Data as Row.

Data as column

Data as column is the default format used in pivot views. In this format, when you drop multiple columns into the Data shelf, the columns will be arranged horizontally as separate columns in the pivot table as shown in the below image.

Data as row

The Data as row option is applicable only when multiple columns are added to the data shelf. Selecting the Data as row option will transform all the total columns as rows under the column header Data Column. However, you can rename the column header as required by double-clicking it. In the below image, Total Sales and Total Cost are expressed in Data as row format.

Summary Rows and Columns

When you create a Pivot Table, Zoho Analytics automatically adds summary rows and columns to it, based on the summary function that you select. A row displaying summary values of each group of data will also be added, if you have added more than one column to the Row shelf of your Pivot Table. For each column dropped in the Data shelf there will be a summary column added to the right of the pivot table.

Layout

Analytics Plus allows you to customize a pivot's layout, based on the columns dropped in the Row shelf. The following are the available layouts. 

• Tabular layout
• Compact layout

Tabular layout 

The default layout of pivot tables, this layout displays the values in the Rows shelf as separate columns in the pivot table. 

Compact Layout 

A close packed view of the pivot table, this layout groups and displays the values in the Rows shelf as a single column in the pivot table.

Note: The compact column that displays the values dropped in the Row shelf will be named Row Labels. To modify the name, in the Edit Design mode of the pivot, navigate to the Row Label column and click the Edit icon that appears near the column name on mouse over. Modify the name as needed, and hit Enter.

To learn more about editing the pivot layouts, click here. 

 Applying Functions on Columns

Zoho Analytics allows you to apply functions like Sum, Count, Average, Min, Max, Year, etc., on the columns to group and summarize data in the Pivot table you create. When you apply a function on a column, a single value will be returned, derived based on the values in the column.
In Zoho Analytics, list of Aggregate/Categorical Functions that you can apply on a column varies depending on the data type of the column. The default function for a Numeric (including Currency) data type is Sum and for a Date data type it is Year. If the data type of the column is a string (Categorical/Dimension column) and not numeric, then the default function applied is Actual Values.

To change the this default function that is applied:

• Open the Pivot table in the Edit Design mode, after dropping the column select the required function (aggregate/category) from the drop-down list present on the column. The drop-down list displays all the applicable functions based on the data type of the column, as shown in the screen-shots below.
• Once you select the aggregation that you would like to use, you can click the Click here to Generate Pivot link or you can click the View Mode button in the toolbar.

For instance if you want to find the average sales of each product by product category in each region, select Average function from the drop-down list for Sales (Data) column and then click Click here to Generate Pivot link or View Mode button in the toolbar.

The following tables list all the functions along with the description of their functionality that are applicable for the Rows and Columns field.

Numeric and Currency Data Types:

Date Data Type - Actual values:

Date Data Type - Seasonal Value:

String(Categorical/Dimension) Data Type:

The following tables list all the functions along with the description of their functionality that are applicable for the Data field.

Numeric and Currency Data Types:

Date Data Type:

String(Categorical/Dimension) Data Type:

 Advanced Summarizing Options

When creating pivots, you can choose how to summarize data i.e., by Sum, Max, Min, Average or by Count in a column, depending on its data type. Apart from these, Zoho Analytics also provides advanced summarizing options, which allows you to summarize data values in a column based on other values in the column. For example you can display a running total of sales over each year or you can display the percentage for each region's sale of a product compared to the total sales.

Following are the functions available for advanced summarization:

 Applying Filters

A useful feature of pivot tables is that you can filter columns, to display only required data. Zoho Analytics allows you to filter a column for specific ranges, individual values, date ranges, etc. depending on the data type of the column. Zoho Analytics offers various filtering options like filter based specific numeric ranges, date ranges, individual values, top 10, bottom 10 etc., Zoho Analytics also allows you to apply multiple filters (based on multiple columns) on a pivot . The filtering option discussed in this topic can be applied only when you are designing a Pivot Table (i.e., when you are in design mode) and not in View mode.

To apply a filter, after you have created the required Pivot view:

• Click Edit Design option in the tool bar.
• Click Filters tab next to the Pivot tab in the Design Area.
• Drag and drop the required columns in to the Filters shelf.
• Once a column is dropped, a list of all the possible options available for filtering the data will be displayed in the box right to Filters shelf as shown in the screen shot below.
• Filter Options vary depending on the data type of the dropped column.
• After you have dropped the column, select one or more values to filter by. Selected filter items appears in the third box on the right.
• Click the button Click Here to Generate Pivot or View Mode button in the toolbar. Data that matches the filter values that you have applied will be displayed. However, if you want to exclude specific data from the view, select Exclude Items option in the drop down list at the top of the filter items box.

The Filtering options are the same for all types of reports that you create in Zoho Analytics namely charts, pivot table & summary views. The filtering options provided varies based on the data type of the column dropped. Refer Filters section under Charts topic to know about various Filter Options.

 Providing User Filters

Zoho Analytics also allows you to include dynamic filters in the Pivots view mode called User Filters. This enables your users who access the report, to apply filters on the report data displayed using the filter columns exposed as part of the User Filters. The filter columns included in User Filters can be displayed using a variety of display components like Drop Down boxes, Slider, Date range chooser etc., to suit your needs.

The steps to provide User Filters are same for all types of reports that you create in Zoho Analytics. Refer User Filters section under Charts topic for details on how to provide User Filters for various column types (Data types).