[Solved] OO Datapilot vs. Excel datatable

[cschoner]

Hi everyone,

I am a newbie to Open Office, but an experienced Excel user. I have created a simple "data table" in Excel, but I have been unable to duplicate this functionality in Open Office Calc. Basically, in Excel I have a database range of cells that looks a like this:


SYMBOL TRAN DATE SHR_BUY SHR_SEL SHARE-$ INVESTED SOLD DAYS

AA 2009/01/14 100.000 0.00 9.02 901.9 0 14
AA 2009/01/15 150.000 0.00 8.99 1348.35 0 13
ACH 2008/08/29 100.000 0.00 22.34 2234.00 0.00 152
ACH 2008/08/29 100.00 0.00 22.41 2240.88 0.00 152

And I have defined a Data Table that produced the following output in a defined range:


SYMBOL SHR_BUY SHR_SEL INVESTED SOLD DAYS

AA 250 0 2250.25 0.00 14
ACH 200.00 0.00 4474.88 0.00 152.00

The important thing here is that the data table below shows just one row (a total row) for each group of rows identified by "SYMBOL" in the data range show above. The data table in effect summarizes the data range values shown above.

I have not been able to duplicate this with the datapilot feature. Instead, datapilot gives is just basically outputting the same data as my source rows (not the summary I require):

SYMBOL SHR_BUY SHR_SEL INVESTED SOLD DAYS

(empty) (empty) (empty) (empty) (empty) (empty)
ACH 100 0 2234 0 152
ACI 0 100 0 6375 267
100 0 6100 0 281
AGU 0 40 0 3420 286
50 0 3260 0 124
70 0 5740 0 279


Can anyone tell me what I am do wrong, or point me to an example of how to duplicate Excel's data table feature?

Thank you,

Carl

[Hagar Delest]

For the tables in your post, you should use the Code BBCode (buttons above the message area) because the tabs and spaces are re-wrapped by the php code.

[Villeroy]

Right-click>Start...
[More Options...]
[X] Ignore empty rows

Are the figures in the source table true numbers or Excel-figures (strings pretending to be numbers)?

[cschoner]

Hi,

Sorry about the codes/tab reformat issue. Yes, the numbers are numbers, the dates are date fields, and the SYMBOL field is a text field. Everything sums up perfectly in Excel, but Calc does not. I have been unable to find any documentation directly comparing Excel's single-variable datatable feature to Calc's Datapilot or Multiplefunction capabilities, but I am assuming since a summarized datatable function is so critical to speadsheet analysis, it must exist in Calc!


Carl

[Villeroy]

You can create a functional new data pilot in Calc?
Select list (or entire columns of)
menu:Data>DataPilot>Start...
[X] Yes, current selection [OK]

Next dialog...
[More Options...]
Target: A1 of a blank sheet (the default is nonsense)
check all other options (won't bite you)
Drag ćategory fields into "Row Fields"
Drag numeric aggregations into "Data Fields"

It's like the pivot tables in Excel '95.
Calculated fields must be calculated in the source range.
There is a limit of 8 fields at a time.

When the pilot is done:
You can group numeric categories by scales and dates by time intervals (F12)
You can change the pilot-specific cell styles in the stylist (F11)
Number formats of data fields are taken from the source field.

[cschoner]

Well, I tried what you suggested. Thank you. That gets me a lot closer to what I need (which is the summary totals) although they are displayed downwards in columns rather than horizontally in rows. What I am getting is:

SYMBOL
ACH ACI AGU AOB
BUY x x x x
SELL x x x x
INV x x x x
SOLD x x x x
Total Sum - SHR_BUY x x x
Total Sum - SHR_SEL (etc)
Total Sum - INVESTED
Total Sum - SOLD



When all I really want is:

SYMBOL
BUY SELL INV SOLD
ACH x x x x
ACI x x x x
AGU x x x x
AOB x x x x

where "x" represents the summary totals. It is this layout that i am unable to reproduce in Calc. I need the bottom table layout since so much of the rest of my application is really dependent on it, and I want the spreadsheet to transportable between Excel and Calc.

Carl

[Villeroy]

Cell cursor in the pilot call menu:Data>Pilot>Start... (or form context-menu). Darg around the category fields from "Row Fields" to "Column Fields", play with "Page Fields". Then try dragging around the grey boxes slowly directly on the sheet and watch the target cursor before you drop. All this does not affect your source data.

[cschoner]

Well, thank you again for your help. I have tried all of your suggestions but I still cannot get the layout I am looking for. No matter what I try, I cannot get my data range which looks like this:

SYMBOL BOUGHT SOLD
ACI 100 0
ACI 300 0
ACI 0 150
AGU 225 0
AGU 0 50

to look like this in my Data Pilot output range:

SYMBOL BOUGHT SOLD
ACI 400 150
AGU 225 50

I always get multiple rows for each one of the various symbols, and this defeats the purpose of a summary report! At this point I do not think it is possible, and if it is not, I would have to view that as a major limitation within Calc.

Carl

[cschoner]

As another example of the importance of this data table concept, please consider the attached example speadsheet. It is a two-variable data table that takes its input from the data range A2:F25 (A simple checkbook register) and creates the data table in range L1:S25, which totals each expense category by month and displays each of these months totals horizontally, in a single row. This is essential to my needs, but It seams in Calc, Data Pilot totals cannot be displayed this way.


Carl

[Villeroy]

If appearance is so much more important than result, use the software which meets your requirements. I can not see any relation between the two tables on your sheet. This is obviously a task only Excel can do.

[cschoner]

Actually, in this case appearance and result are one in the same; the result that I was seeking is not possible in the absence of the layout I required. Every good spreadsheet must have the capability to take a checkbook-style register and display it as a summary with the Account codes listed down the left side of the sheet, the months displayed from Jan to Dec from left to right across the top row, and totals filling the matrix defined within. Fortunately, Calc does have a means to reverse/invert a range of cells that look like this:

Name1, Address1, URL1
Name2, Address2, URL2
Name3, Address3, URL3

into this format:

Name1, Name2, Name3
Address1, Address2, Address3
URL1, URL2, URL3

and vice-a-verse.

I discovered that once you have created a data pilot table that looks like the first example I presented in this thread, you can "transpose" the rows and columns so that the sheet appears (and therefor displays totals) as I require. To to this, you mark the entire data table range, cut it, and then select "edit/paste special" and check the "Transpose" box. The result will then be displayed correctly.

Thank you with your assistance with this. I would not have discovered this so quickly feature on my own.

Carl

[Villeroy]

Accounting in spreadsheets is a reason to get fired. There is not a single professional accounting software on the market which makes use of spreadsheets, except for reporting (output only). Databases are the tool of choice when you are in need to fiddle with your own home-brew accounting model.
In a database you have special data types to calculate currency figures without rounding issues, you can enforce unique entities with referencial integrity (no transaction without existing accounts and invoice number, no invoice without full client information, ...).
You can use the stored data in a wide variety of software tools, including spreadsheets. Let alone issues of performance, reliability, data safety and security against user-corruption.

OK, if the 12 column fields in the right table of your example sheet are supposed to represent months:
Call the wizard for your data pilot.
Drag "DATE" into "Row Fields".
Finish the wizard.
Click a date cell in the pilot and call Data>Outline>Group (shortcut F12)
Group by Years and Months.
Drag new fields "Year" and "Month" to column fields.
Make shure that both units, month and year, are selected. Month "April" without year summarizes the amounts of all Aprils.

[cschoner]

Actually, as a professional Oracle DBA I am well versed in the advantages that a database offers over a spreadsheet for certain applications. However, there is a certain class of people (non-accountants) who prefer to keep simple lists of data in columnar format, and manipulate that data to suit their own personal needs without the constraints or learning curve that most database applications impose. A simple, personal checkbook register or a summarized list of daily transactions of any kind are perfect examples of this type of application, and if a spreadsheet package can't handle these, then it has little inherent value. Fortunately, though, as I stated in my previous post, Open Office Calc does have these capabilities built into it; I can easily port my personal applications from Excel to Calc with little more difficulty than learning the different methods that Calc employs to achieve the same results that I have grown dependent on in Excel. I have learned much more about Calc's capabilities since my last post, and see now that is is quite capable of doing everything that I would expect a good spreadsheet to do.

[Villeroy]

I have learned much more about Calc's capabilities since my last post, and see now that is is quite capable of doing everything that I would expect a good spreadsheet to do.

Creating groups of years and months from the date-column solves your problem?

[cschoner]

My problem is solved! Calc is a great spreadsheet, and I can now live without Excel! Thank you for your help!

Carl

[acknak]

I'm coming in late here, and it seems I've missed the party, but I was rather surprised that Calc's Datapilot feature is unable to create a simple summary of a table.

I'm not that familiar with the DataPilot, but as I played with different layouts, it became clear to me that a summary of a table is a different operation than a datapilot or pivot table. The summary is not rearranging or transposing the table at all, whereas the datapilot always wants to create a table with a new structure.

So, for the sample input, the best I can do with a datapilot is:

dpilot_1.png (3.47 KiB) Viewed 9621 times

But with a different arrangement of the input data, the datapilot can create what you want:

dpilot_2.png (3.85 KiB) Viewed 9623 times

The summary analysis can also be done by the Data > Subtotals function, but the results are mixed into the input data. You can hide/expose the different levels using the grouping buttons at the left:

dpilot_3.png (10.5 KiB) Viewed 9621 times

Sorry if this is just belaboring the obvious--I'm trying to learn how all this fits together myself.