Qlik Sense Pivot Table – Learn Pivoting in Qlik Sense

FREE Online Courses: Your Passport to Excellence - Start Now

Earlier, we discussed Maps in Qlik SenseToday, we will see the Qlik Sense Pivot Table. In this tutorial, we will learn about a special kind of table known as the pivot table. Also, we will see how different is the pivot table from the normal table and how to use it. 

So, let’s start Qlik Sense Pivot Table.

Qlik Sense Pivot Table - Learn Pivoting in Qlik Sense

Qlik Sense Pivot Table – Learn Pivoting in Qlik Sense

1. What is Qlik Sense Pivot Table?

A pivot table consists of rows and columns which contains dimensions and measure fields. We can switch the position of dimensions and measures between columns and rows in a pivot table. We call it pivoting. By pivoting the view of table changes and the field values are shown accordingly. In a pivot table, you can view multiple dimensions and measures interchangeably in different views.

Recommended Reading – Tables in Qlik Sense

Qlik Sense Pivot Table

View Multiple Dimensions and Measures

Shown above is a pivot table having three dimensions on the left in the row section and two measures Quantity and Sales on the right section which collectively displays all the measure fields as columns. You move dimensions or measures from column to rows or rows to columns section by dragging them.

For instance, if we move the dimension Customer to the columns section on the right, the table will be updated to show Quantity and Sales for each customer.

Qlik Sense Pivot Table

Quantity and sales for each customer

2. How to Create a Qlik Sense Pivot Table?

In order to create a Qlik Sense Pivot Table, follow the steps below.

Step 1: Open the editor of the sheet of the application in which you want to create a pivot table. The editor is opened from the Edit option present on the toolbar of the sheet.

Step 2: Select the option Pivot table (from the Assets Panel) and add it to the editing grid at the center by dragging and dropping.

Qlik Sense Pivot Table

Select Pivot Table from Asserts Panel

Step 3: Add a measure and dimension to start with creating the table. You add more columns in the table from the properties panel at the right of the editor.

Step 4: Once you are done with selecting and adjusting all the aspects of the table from the properties panel, the table will be displayed on the editor instantly. Click Done to bring it on the sheet.

Qlik Sense Pivot Table

Displaying the Table

3. Properties Panel in Qlik Sense Pivot Table

From the properties panel present at the right of the editor, you can manage different aspects of the pivot table and modify it according to your requirement.

Have a look at Qlik Sense Line Charts

i. Data

From the data tab, you can add dimensions and measures as row and column for the table. You can do the pivoting of dimensions and measures from here also by dragging the field names up and down.  

Qlik Sense Pivot Table

Add Dimensions and Measures

You can edit each column individually by clicking on the arrow given beside the name of the column. The options include expression, label, number formatting (if it is a measure), etc.

ii. Sorting

From the sorting tab you can sort the dimensions and measures in different ways, like sort by the expression, sort by load order, sort numerically, sort alphabetically etc.

Qlik Sense Pivot Table

Sorting in Qlik Sense Pivot Table

iii. Add-ons

In this section, you can use the data handling option to apply certain display conditions on the data used in the pivot table. The ‘Include zero values’ option includes all the 0s that are present in a measure field as value and displays them.  

Qlik Sense Pivot Table

Add-ons section in Qlik Sense

iv. Appearance

The appearance section contains all the setting related to the appearance of the pivot table.  

The first section is the General section has options for showing or hiding titles or details. The Presentation section gives you options to fully expand the table without any collapsed rows and to indent rows.

Qlik Sense Pivot Table

Presentation Section in Qlik Sense Pivot Table

If you do not select the Fully expanded option, then the table will look as shown in the image below.

Qlik Sense Pivot Table

Fully expanded option is not selected

But, if you select the Fully expanded option, then the table is expanded to show all the details and values.

Qlik Sense Pivot Table

Fully expanded option is selected

Do you know about Qlik Sense Scatter Plot Visualization?

4. Pivoting in Qlik Sense

In a Qlik Sense Pivot Table, pivoting can be done in two ways, i.e. either from the properties panel or from the pivot table present on the sheet. Refer to the table that we created in the ‘Creating a pivot table’ section. We can apply pivoting by moving the dimension ‘Product_line’ to the measures/columns section. The updated pivot table is shown in the image below. As you can see, it shows average cost and total units for each product_line like Apparel & Accessories, Arts & Entertainments, Hardware etc. In each of these columns, the values are shown corresponding to the product category values and other cells are left blank with a dash in them.

Qlik Sense Pivot Table

Pivoting in Qlik Sense

You can also apply limitations or conditions on the data set and do global grouping which reflects on all the dimension and measure values. This is done from the data section of the properties panel.

Qlik Sense Pivot Table

Apply limitations and conditions on the data set

For instance, in the table shown below, which has names of sales representative and year. On the other hand, the column section is the sum(Sales) measure.

Qlik Sense Pivot Table

Qlik Sense Pivoting

We will apply limitations on this table such that only those sales representative’s names will be shown which have the top 5 total sales. This limitation or condition will be applied to all the year through global grouping.

Qlik Sense Pivot Table

Applying limitations on the table

Thus, the new pivot table will only show the top 5 sales representative for each year.

5. Advantages and Limitations

The biggest advantage of using a pivot table is that you can view data in different forms with different perspectives. You can shuffle between rows and columns and dimensions and measures to gain new views of the table. You can use multiple dimensions and measures in pivoting. Although, for some users what comes out as a limitation is that pivot tables might seem complicated to deal with sometimes and requires some skills.

Let’s revise Qlik Sense Logical Functions

So, this was all in Qlik Sense Pivot Table.

6. Conclusion

Thus, this was all that we needed to learn in Qlik Sense Pivot Table. We hope this information helped you in creating a pivot table using your data. Still, if you feel any query related to Qlik Sense Pivot table ask in the comment section. 

You must check – Qlik Sense Financial Functions

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

4 Responses

  1. jithendra says:

    how to add up and down arrow based on a condition in qlik pivot , assume if(Sum(measure)-Above(Sum(measure))>0,green(),red()) ,, all green should show up arrow and all red is down arrow

  2. Laurent says:

    This widget is not usable for users in the real life. for me it sounds crazy to deliver this kind of tool while some basic and common actions are not present for the basic user:
    – No ability to expand 1 dimension only (!!!???),
    – Impossible to sort data on sub dimensions
    – No ability to add or remove dynamically a total or sub total,
    – Export to excel: it exports the Photo of the pivot table, not the flat data. Obliged to use the table widget to do that
    – Export to excel : the dash in case of null values in measures, is exported as is, so you have text and number in same column, and the user needs to each time remove manually all the dashes in the exported file.

    Most of that was present in the QlikView pivot table component …

  3. Kiran says:

    How to add hyperlink in the values of pivot table and navigate to the detailed view?

  4. shivangi says:

    If i had created two pivot tables and wish to apply filter only on one.For example,i have data with traffic from mobile and desktop.so one pivot table only with mobile and other only with desktop.Can we do that?I do not want filter for entire sheet.

    Thanks.Awaiting reply.

Leave a Reply

Your email address will not be published. Required fields are marked *