QlikView - Pivot Tables


Pivot Tables are widely used in data analysis to present sum of values across many dimensions available in the data. QlikView's Chart option has the feature to create a Pivot Table by choosing the appropriate chart type.

Input Data

Let us consider the following input data, which represents the sales figure of different product lines and product categories.

Product_Line,Product_category,Value
Sporting Goods,Outdoor Recreation,5642
Food, Beverages & Tobacco,2514
Apparel & Accessories,Clothing,2365
Apparel & Accessories,Costumes & Accessories,4487
Sporting Goods,Athletics,812
Health & Beauty,Personal Care,6912
Arts & Entertainment,Hobbies & Creative Arts,5201
Arts & Entertainment,Paintings,8451
Arts & Entertainment,Musical Instruments,1245
Hardware,Tool Accessories,456
Home & Garden,Bathroom Accessories,241
Food,Drinks,1247
Home & Garden,Lawn & Garden,5462
Office Supplies,Presentation Supplies,577
Hardware,Blocks,548
Baby & Toddler,Diapering,1247
Baby & Toddler,Toys,257
Home & Garden,Pipes,1241
Office Supplies,Display Board,2177

Load Script

The above data is loaded to the QlikView’s memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the "Table Files" option from the "Data from Files" tab and browse for the file containing the above data. The following screen appears.

1_File_wizard

Click "OK" and press "Control+R" to load the data into QlikView's memory.

Select Chart Type

Next, we use the chart wizard to select the Pivot Table option. Click Next.

2_pivot_select_chart

Select Chart Dimension

In the next screen, we choose Product_Line as the first dimension for the chart.

3_pivot_choose_dimension

Select Chart Expression

The next screen prompts us for selecting the chart expression where we choose the sum of value.

4_pivot_choose_sum

Select the Chart Format

On clicking next, we get the screen to choose chart format in which we select Pyjama Green as the style and the default mode.

5_pivot_chart_style

Pivot Chart Data

Completing the above steps gives us the final chart as below.

6_pivot_pivot_final
Advertisements