Pivot Value Column Feature

This document describes the use of a value column list in a pivot table. This option is available on selected rows and columns in the pivot table. Users can change this by clicking on the settings icon available on the right side of the selected row or column.

Objective

The objective of this document is to understand the Value column feature in the Pivot table and how you can pass column data on click even if you haven’t used it in the pivot.

Prerequisites

sales1.ds

Download dependency files from here

Steps to Follow

Follow the steps below to understand this functionality:

Create/Open the dashboard and add a pivot from the widget library.
Select the dataset sales1.ds from the dataset dropdown and drag & drop columns as shown in the image below:
- Rows: country
- Columns: state
- Values: Sum of creditLimit
Click on save and exit, and resize the pivot widget.
Hover over the widget and click on widget edit to open the widget edit window.
When you click on the settings icon of the selected row or column, by default, the value list column or row name is selected. So, when the user writes an event to pass the row or column name on the click, it will pass the selected row or column value. Let’s understand this by example and create an event in a pivot that passes the clicked row or column value and filters table data in the next tab.

To add an event, click on the settings icon near the country column.

Once clicked, several options will be displayed as above. In the above screenshot, you can see Country is selected in the Value list.
To add a script, click on the script checkbox and the script space will be added under the script checkbox as shown below:

In this script box, write a script to take the column country value, go to the next tab, and filter the table.

Sample Script

The sample script used here is below:

Please find the sample script here:

var tab = new CustomEvent("aiv-change-tab", 
{ "detail": 1 });
window.dispatchEvent(tab);
var filter = new CustomEvent("aiv-filter-call", 
{ "detail": {"country": '{{country}}' }});
window.dispatchEvent(filter);

Script description is as follows:

Selected column name
Filter name which will be created in the second tab for filtering the table
Event to call the filter
Tab ID on which I want to switch
Event for tab change

Click on the accept button to save the changes.

Click on the save and exit button to save the changes.

Create a static filter with the same name as provided in the event (i.e., country). In step number 2, keep the filter on “on change”.

Create a new tab and add a table widget there with the Pivot Value Column Data dataset.

Add the same filter you created just one step above in this tab as well. Link this filter with a country column of the table widget. Your tab 2 will look like this:

And Tab 1 will look like this:

Let’s check this event by clicking on any country in the pivot table. By doing so, it will take you to tab 2, and table data will be filtered with the country you clicked.

In the previous case, whatever country we clicked is passed to the next tab because in the value list by default country column is selected.

If the user wants to pass some other column data associated with this country column, the value list comes into the picture. For example, if I want to pass the country associated with the clicked country, then we use the country in the value drop-down list.

The dataset we are using has a unique selected country for each country. For demonstration purposes, this dataset is designed this way.

Go to pivot, edit widget, and click on the settings icon to open settings.

From the value drop-down, we are selecting the country column.

This means now when we click on any country name it will pass the selected coutry.

Now we have linked the filter in tab 2 with the country earlier but now we are passing the country name. So, change the filter linking with the country column this time and delete the existing linking with the country.

Save changes and go to preview mode.

Now click on any country and it will take the country data for that country from the dataset and pass it to the next tab filter.

So now the table widget will filter with the country value of the clicked country. For example, if I click on the country Canada, it will pass the country value Canada in the event and filter tab 2 tables with this country value.