We are going to select the pay_year and pay_week columns from the Avaiable Fields which will ‘spread’ the customer totals by year/week. Column Fields – Whatever you put here will be a column in the resulting Pivot Table.In our case we would like to know the total owed by each customer so we should drag the customer column from the Available Fields list here Row fields – This gives us the aggregation option for rows.The column selected will be aggregated by the values defined in the Row and Column fields Columns in this area are aggregated - we can select the ‘base_os_value’ column which represents the outstanding value of each transaction in base currency (in this case Sterling). Data Fields – This area must contain at least one ‘measure’.For example, we will allow selection by sl_analysis so this field will be dragged to the Page Fields section Page Fields – You can limit the data to one or several values in one of your columns.To use the columns from the data set in the Pivot Table you just drag-and-drop each field to the appropriate area in the Layout dialog as follows: If you are following along with the provided files (see blow) and for some reason you do not get all of the columns, highlight the whole range in the data set before you create the Pivot Table. The box on the right right side of this dialog lists all of the columns from the csv that we imported earlier. You should get a dialog that looks like this: Once the data is imported into LibreOffice click on a cell somewhere within your data range, then go to Data–>Pivot Table–>Pivot Table, leave the source as ‘Current selection’, and click OK. For our purpose we can just use the dialog defaults. There are also options to select alternative column separators, the character set of the data etc.
LibreOffice will attempt to infer the column type (date, number, string etc) from the data although the import process does allow for forcing column types if the auto-detection fails. The sl_openitems.csv we are using is separated by commas with the first row being the column headings: You can open a csv file directly in Libre Office - this presents the following window:
There is an individual row for each invoice, credit note, journal or unallocated receipt meaning that the data is naturally in ’long format’ which is required as a building block for pivot tables. The power of pivot tables comes from using raw data - the example in this blog post is created using a csv file generated from uzERP example data showing open (unallocated) sales ledger transactions.
#More than two active tables in libreoffice base form how to#
This post walks you through how to set up a Sales Ledger Receipts forecast using a Pivot Table in LibreOffice.
Pivot Tables are an extremely useful analysis tool - they are very flexible, powerful, and can generate valuable insights from large amounts of data if used properly.
0 kommentar(er)
