Concept"

From Documentation
Line 30: Line 30:
 
[[Image: ZKPivotEsn_work_pivot_06.png]]
 
[[Image: ZKPivotEsn_work_pivot_06.png]]
  
We call ''Agent, Airline'' and ''Price'' '''fields''', and more precisely speaking, ''Agent'' is a '''row field''', ''Airline'' is a '''column field''', and ''Price'' is a '''data field'''.
+
We call ''Agent, Airline'' and ''Price'' '''fields''', and more precisely speaking, ''Agent'' is a '''row field''', ''Airline'' is a '''column field''', and ''Price'' is a '''data field'''. <!-- TODO: areas: row, column, data, titles -->
  
  
Line 62: Line 62:
  
 
We call this property '''data field orientation''', or '''data orientation''' in short. In the former case, the data orientation is "column", while in the latter, it is "row".
 
We call this property '''data field orientation''', or '''data orientation''' in short. In the former case, the data orientation is "column", while in the latter, it is "row".
 +
 +
  
 
==Summary and subtotal==
 
==Summary and subtotal==
<!-- Subtotal -->
+
So far we have seen ''Price'' and ''Mileage'' as data fields, and the data cell values are sums of a subset from raw data. What if a data field is not numeric? How can we sum them up? In this case, Pivottable is smart enough to replace the concept of '''Sum''' by '''Count'''. For example, if you put ''Price'' and ''Customer'' as data fields, you will see:
 +
 
 +
[[Image: ZKPivotEsn_work_pivot_18.png]]
 +
 
 +
Thus, to be generic, rather than calling the displayed values "sum", we call them '''summary''', which could be a sum, a count, or even anything defined by user.
 +
 
 +
In additional to changing summary, we can also attach a row or column for '''subtotal''' on each header node. For example, if we let ''Agent'' and ''Customer'' be row fields, and want a subtotal of as '''Average''' on ''Agent'' layer, we will see:
  
 +
[[Image: ZKPivotEsn_work_pivot_19.png]]
  
<!--
 
==More terms and trivia==
 
-->
 
  
  

Revision as of 07:39, 29 March 2011


WarningTriangle-32x32.png This page is under construction, so we cannot guarantee the accuracy of the content!

If you are not familiar with the concept of pivot table, this section will kindly guide you through.

The Basic

Suppose you have a table of data of flight ticket sales record like this,

ZKPivotEsn raw table 03s.png


and someone asks you "Hey, how did Antonio do this week? How much did he sell from each airline?"

You read through the table, highlight all entries with Antonio involved, categorize them by airlines, and sum up the selling prices.

ZKPivotEsn raw table 04s.png


This is a typical scenario when you work with business data, and this is where a pivot table comes into play.

For example, you can let Pivottable sums up all the selling prices per agent, per airline, and arrange agents on the row, airline on the column, then you automatically have the desired data summary on Antonio's row.

ZKPivotEsn work pivot 03.png

Taking a closer look, the number in each cell comes from summing up the values of all price entries of given agent and airline:

ZKPivotEsn work pivot 06.png

We call Agent, Airline and Price fields, and more precisely speaking, Agent is a row field, Airline is a column field, and Price is a data field.


Row and column headers

The computer screen is 2-dimensional after all, so what if we want to summarize data by more than 2 criteria?

Pivottable can accept multiple fields on columns and rows. You can think of it as Auxheaders of Grid. For example, if you want to have Agent and Customer on the rows, Airline and Flight on the columns, this is what you will get:

ZKPivotEsn work pivot 13.png

You can also think of row and column fields as categorization layers of data. On the X dimension, the data are categorized firstly by Airline, then by Flight. While on the Y dimension, they are categorized by Agent then Customer. Each of these two categories (row and column) has a tree structure. We call them row header tree and column header tree, respectively.

ZKPivotEsn work pivot 14.png

In addition, it is quite easy to imagine, if you click on the minus sign on any of the row or column header node, you can close the node so the data will be contracted into a single row or column.

ZKPivotEsn work pivot 15.png


Data fields

We can multiple row fields and column fields in a Pivottable, then how about data fields? Yes, we can surely have more than 1 data fields. Suppose we have Price and Mileage as data fields, you will see:

ZKPivotEsn work pivot 16.png

By default, the data fields are displayed by column, but you can also have them arranged on rows.

ZKPivotEsn work pivot 17.png

We call this property data field orientation, or data orientation in short. In the former case, the data orientation is "column", while in the latter, it is "row".


Summary and subtotal

So far we have seen Price and Mileage as data fields, and the data cell values are sums of a subset from raw data. What if a data field is not numeric? How can we sum them up? In this case, Pivottable is smart enough to replace the concept of Sum by Count. For example, if you put Price and Customer as data fields, you will see:

ZKPivotEsn work pivot 18.png

Thus, to be generic, rather than calling the displayed values "sum", we call them summary, which could be a sum, a count, or even anything defined by user.

In additional to changing summary, we can also attach a row or column for subtotal on each header node. For example, if we let Agent and Customer be row fields, and want a subtotal of as Average on Agent layer, we will see:

ZKPivotEsn work pivot 19.png


Version History

Last Update : 2011/03/29


Version Date Content
     



Last Update : 2011/03/29

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.