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:

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:

Exercises

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

Leave a Reply