Categories
Developer Teaching QlikView /en

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.

Pivoting made easy

To create a cross table right-click an “empty” area on the work surface and choose:

Screenshot 2015-03-30 14.44.03

Chart! Then the “Wizard” starts which allows you to create a cross table.

Screenshot 2015-03-30 14.45.11
After choosing the type, click on next.

Screenshot 2015-03-30 14.45.36

Select here, after which dimension/category the calculation of the values should happen.Screenshot 2015-03-30 14.45.56
Specify the the wanted function/formula/calculation – here as a simple Count() – Quantity.

Screenshot 2015-03-30 14.46.22

You can install multiple formulas by clicking on “add”.

Screenshot 2015-03-30 14.46.40
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.

220px-Set_subsetAofB.svg

Complete coalition “*”.

220px-Venn0001.svg

Only the intersection “+”.

220px-Venn0100.svg

Only in A “-“.

220px-Venn0110.svg

Only in A or B – “-P( )” – “E( )”.

220px-Venn0111.svg

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.

Leave a Reply