## 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.

## Pivoting made easy

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
//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.