Categories

# Lesson 9 – Basic pivot functions

## Overview

In this lesson we are going to learn about the most important aggregation functions in detail and how to “modulate” them to our purpose. A short introduction into the set-analysis is going to show us one of the most powerful functionalities.

To create a cross table right-click an “empty” area on the work surface and choose: Chart! Then the “Wizard” starts which allows you to create a cross table. After choosing the type, click on next. Select here, after which dimension/category the calculation of the values should happen. Specify the the wanted function/formula/calculation – here as a simple Count() – Quantity. You can install multiple formulas by clicking on “add”. Our cross table now shows the quantity of the plannings per product group.

Please note that there is a big difference between “clearly” counted and counted values. To really count only unique values, use the DISTINCT command.

The most commonly used functions in practice are:

```//SUM(Sales)

//AVG(Sales)

//MAX(Sales)

//MIN(Sales)

//COUNT(Sales)

//SUM(Sales) / COUNT(Sales)

//SUM(TOTAL Sales) or SUM({1} Sales)```

The last expression, which represents the first reference to the set-anlyses is especially interesting.

## Set-analyses

A set is a mathematical amount. This amount can be, for example, cut with another amount or else be excluded. Complete coalition “*”. Only the intersection “+”. Only in A “-“. Only in A or B – “-P( )” – “E( )”. Both amounts united.

To better understand the principle, we are going to look at the following examples:

```//simple sum of the Sales
SUM(Sales)

//Sum of the sales in 2014
//The Dollar sign stands for the current selection
//If there is no intersection-operator, QlikView goes from a *
SUM({\$<Year={"2014"}>} Sales)

//Sum of sales in 2013
SUM({\$*<Year={"2013"}>} Sales)

//Sum of sales in 2013 and 2014
SUM({\$*<Year={"2014","2013"}>} Sales)

//Sum of all sales less than 2013
SUM({1-<Year={"2013"}>} Sales)

//Sum of all sales without 2013 and possible current selection
SUM({1-<Year={"2013"}>*\$} Sales)

//filter on 2 columns
SUM({\$<Year={"2013"},Category={"Toys"}>} Sales)

//filter by variable
SUM({\$<Year={"\$(varName)"},Category={"Toys"}>} Sales)

//filter by function result
SUM({\$<Year={"\$(=year(today()))"},Category={"\$(varProductCategory)"}>} Sales)```

## Exercises

• Create a cross-table in which you can choose the sum of the sales by CalendarObject. Tip: Work with variables.