Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/DataPilot functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: {{DISPLAYTITLE:DataPilot functions in detail}} {{Documentation/CG3Ch6TOC |ShowPrevNext=block |PrevPage=Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 3 |NextPage=Documentation...)
 
(3 intermediate revisions by 2 users not shown)
Line 8: Line 8:
  
 
== The database (preconditions) ==
 
== The database (preconditions) ==
The basis for work with the DataPilot is always a list with your data in raw format. Such a list is comparable to a database table. The table consists of rows (data sets) and columns (data fields). The field names are in the first row above the list.
+
The basis for work with the DataPilot is a list of raw data, similar to a database table, consisting of rows (data sets) and columns (data fields). The field names are in the first row above the list.
  
Later we will explain that the data source could be an external file or database. The simplest use case is that your data is contained in a Calc spreadsheet. For such a list, Calc offers sorting functions that do not depend on the DataPilot.
+
The data source could be an external file or database. For the simplest case, where data is contained in a Calc spreadsheet, Calc offers sorting functions that do not require the DataPilot.
  
For processing data in lists, the program must know in which area of the spreadsheet the table is. A Calc table can be anywhere in the sheet, in any position. It is also possible, that a spreadsheet contains several unrelated tables.
+
For processing data in lists, the program needs to know where in the spreadsheet the table is. The table can be anywhere in the sheet, in any position. A spreadsheet can contain several unrelated tables.
  
 
Calc recognizes your lists automatically. It uses the following logic:
 
Calc recognizes your lists automatically. It uses the following logic:
Line 20: Line 20:
 
This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.  
 
This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.  
  
{{Documentation/Caution| No empty rows, no empty columns within lists!}}
+
{{Warn| No empty rows or empty columns are allowed within lists!}}
  
If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected. This might be useful in only very few cases.
+
If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected.  
  
{{Documentation/Caution| For sorting, filtering or using the DataPilot, always select only one cell.}}
+
{{Warn| For sorting, filtering or using the DataPilot, always select only ''one'' cell.}}
  
 
A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.
 
A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.
  
In addition to these formal aspects, the logical structure of your table is very important for using the DataPilot.
+
In addition to these formal aspects, the logical structure of your table is very important when using the DataPilot.
  
{{Documentation/Caution| Calc lists must have the ''normal form''; that is, they must have a simple linear structure.}}
+
{{Warn| Calc lists must have the ''normal form''; that is, they must have a simple linear structure.}}
  
When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section [[Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 1|Sales volume overview]]. This will give you a list of bad ideas, that you can find very often among spreadsheet users who are not informed about the possibilities of processing lists within a spreadsheet.
+
When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section [[Documentation/OOo3_User_Guides/Calc Guide/DataPilot example 1|Sales volume overview]]. Here are some mistakes commonly made by inexperienced spreadsheet users:
  
First bad idea: You could have made several sheets. For example, you could have made a sheet for each group of articles. Analyses are then only possible within each group. Analyses for several groups would then be a lot of hassle.
+
#You could have made several sheets, for example, a sheet for each group of articles. Analyses are then possible only within each group. Analyses for several groups would be a lot of work.
  
Second bad idea: In the turnover list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
+
#In the Sales list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
  
Third bad idea: You could have entered the amounts in chronological order. At the end of each month you could have made a sum. In this case, a sorting of the list for different criteria is not possible, and you could not use the DataPilot. The rows with the sums would be treated by the DataPilot like any other amount you have entered. Getting monthly results is one of the very fast and easy features of the DataPilot.
+
#You could have entered the amounts in chronological order. At the end of each month you could have made a sum total. In this case, sorting the list for different criteria is not possible because the DataPilot will treat the sum totals the same as any other figure. Getting monthly results is one of the very fast and easy features of the DataPilot.
  
 
== Start ==
 
== Start ==
Line 46: Line 46:
  
  
{{CCBY}}
+
{{CCBYSA}}
 
[[Category: Calc Guide (Documentation)]]
 
[[Category: Calc Guide (Documentation)]]

Revision as of 21:51, 13 July 2018



This part describes the use and options of the DataPilot in detail.

The database (preconditions)

The basis for work with the DataPilot is a list of raw data, similar to a database table, consisting of rows (data sets) and columns (data fields). The field names are in the first row above the list.

The data source could be an external file or database. For the simplest case, where data is contained in a Calc spreadsheet, Calc offers sorting functions that do not require the DataPilot.

For processing data in lists, the program needs to know where in the spreadsheet the table is. The table can be anywhere in the sheet, in any position. A spreadsheet can contain several unrelated tables.

Calc recognizes your lists automatically. It uses the following logic:

Starting from the cell you’ve selected (which must be within your list), Calc checks the surrounding cells in all 4 directions (left, right, above, below). The border is recognized if the program discovers an empty row or column, or if it hits the left or upper border of the spreadsheet.

This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.

Documentation caution.png No empty rows or empty columns are allowed within lists!

If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected.

Documentation caution.png For sorting, filtering or using the DataPilot, always select only one cell.

A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.

In addition to these formal aspects, the logical structure of your table is very important when using the DataPilot.

Documentation caution.png Calc lists must have the normal form; that is, they must have a simple linear structure.

When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section Sales volume overview. Here are some mistakes commonly made by inexperienced spreadsheet users:

  1. You could have made several sheets, for example, a sheet for each group of articles. Analyses are then possible only within each group. Analyses for several groups would be a lot of work.
  1. In the Sales list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
  1. You could have entered the amounts in chronological order. At the end of each month you could have made a sum total. In this case, sorting the list for different criteria is not possible because the DataPilot will treat the sum totals the same as any other figure. Getting monthly results is one of the very fast and easy features of the DataPilot.

Start

You start the DataPilot with Data > DataPilot > Start. If the list to be analyzed is in a spreadsheet table, select one cell within this list. Calc recognizes and selects the list automatically for use with the DataPilot.

After the Start of the DataPilot


Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA).
Personal tools