QlikView - Aggregate Functions
QlikView Aggregate functions are used to produce aggregate data from the rows of the table. The functions are applied to the columns when creating the load script. Given below is a sample list of Aggregate functions. We also need to apply the Group by clause appropriately when applying the aggregate functions.
- SUM gives the sum of the numeric values of the column.
- AVG gives the average of the numeric values of the column.
- MAX gives the maximum of the numeric values of the column.
- MIN gives the minimum of the numeric values of the column.
Example
Consider the following data stored as product_sales.csv in the local system. It represents the sales figures for different product lines and product category in a store.
Product_Line,Product_category,Quantity,Value
Sporting Goods,Outdoor Recreation,12,5642
Food, Beverages & Tobacco,38,2514
Apparel & Accessories,Clothing,54,2365
Apparel & Accessories,Costumes & Accessories,29,4487
Sporting Goods,Athletics,11,812
Health & Beauty,Personal Care,21,6912
Arts & Entertainment,Hobbies & Creative Arts,58,5201
Arts & Entertainment,Paintings,73,8451
Arts & Entertainment,Musical Instruments,41,1245
Hardware,Tool Accessories,2,456
Home & Garden,Bathroom Accessories,36,241
Food,Drinks,54,1247
Home & Garden,Lawn & Garden,29,5462
Office Supplies,Presentation Supplies,22,577
Hardware,Blocks,53,548
Baby & Toddler,Diapering,19,1247
Creating the Load Script
We open the script editor in a new QlikView document using Control+E. The following
code creates the required tables as inline data. After creating this script press control+R to reload the data into the QlikView document.
Creating Sheet Object
Let us create a Table Box sheet object to show the data generated by the Aggregate function. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and the select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table
Box as shown below.
Applying SUM() function
Given below is the load script to find the sum of the sales quantity and sales value across the Product Lines and product categories.
Click OK and press Control+R to reload the data into QlikView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below.
Applying AVG() function
Given below is the load script to create the average of the sales quantity and sales value across each Product Line.
# Average sales of Quantity and value in each Product Line.
LOAD Product_Line,
avg(Quantity),
avg(Value)
FROM
[E:\Qlikview\data\product_sales.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Group by Product_Line;
Click OK and press Control+R to reload the data into QlikView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below.
Applying MAX() & MIN() function
Given below is the load script to create the maximum and minimum of the sales quantity across each Product Line.
# Maximum and Minimum sales in each product Line.
LOAD Product_Line,
max(Quantity) as MaxQuantity,
min(Quantity) as MinQuantity
FROM
[E:\Qlikview\data\product_sales.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Group by Product_Line;
Click OK and Control+R to reload the data into QlikView document. Now follow the same steps as above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below.
