Summary

Creates a pivot table with the given number of columns and rows

Please see Create pivot table 5.0


Configuration

Input settings of existing table

Name

Value

Opt.

Description

Example

X-Axis Column

System.Object

-

This column is used in the X-Axis calculation

-

Y-Axis Column

System.Object

-

This column is used in the Y-Axis calculation

-

Settings

Name

Value

Opt.

Description

Example

Summation method

System.String

  • Number
  • Percentage column
  • Percentage row

-

Select the method how rows and columns are summarized

-

Number of columns

System.String

-

Number of columns for the X axis:

  • Enter the number of columns required
    • e.g. 3
  • Using the STEP function (Interval;Minimum;Maximum)
    • The Minimum and Maximum parameters are optional
    • The separator = ;
    • Interval = Value range of a column
    • Minimum = Smallest value for column start
    • Maximum = Largest value for column end
    • e.g. STEP(35;5;250)
    • e.g. STEP(20)
  • Using the INTERVAL function (From-To;......)
    • Free definition of column intervals. Any number of definitions are possible
    • The separator = ;
    • From = Interval start value
    • To = Interval end value
    • e.g. INTERVAL(17-34;68-211;)
    • e.g.  STEP(0-99)

-

Ignore NULL (X-Column)

System.Boolean

-

When activated all rows with invalid (NULL) values are ignored

-

Ignore 0 (X-Column)

System.Boolean

-

When activated all rows with the value 0 in column X are ignored

-

Number of rows

System.String

-

Enter the number of rows to be displayed

-

Ignore NULL (Y-Column)

System.Boolean

-

When activated all rows with invalid (NULL) values are ignored

-

Ignore 0 (Y-Column)

System.Boolean

-

When activated all rows with the value 0 in column Y are ignored

-

Hide totals row

System.Boolean

-

If this option is active, no totals row will be displayed in the pivot table

-

Want to learn more?

Examples

Example 1: classify in pre-defined intervals

Task and Settings

Result

Given a list of work records with employee and duration in minutes

Pivot table classify duration in

  • >15 min
  • >30 min
  • >1 h
  • >2 h
  • >3 h
  • >4 h
  • >1 d (8 h = 1 d workday)
  • >2 d
  • >3 d
  • >5 d (5 d = 1 w workweek)

INTERVAL(1-15;16-30;31-60;61-120;121-180;181-240;
241-480;481-960;961-1440;1441-2400;2401-9999999)



Example 2: classify in a number of classes

Task and Setting

Result

The range of observations will be classified in the specified number of equally large intervals.

Set the Number of columns to 5


Troubleshooting

Problem

Frequent Cause

Solutions

Columns are not sorted in the correct order

The pivot table sorts columns according to how the data appear in the original table.

Re-order the data in the original table.


Related topics