[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
zhangweiwu
OOo Enthusiast

Joined: 19 Jan 2005
Posts: 132

 Posted: Fri Dec 02, 2005 4:52 am    Post subject: newbie: 3-dimensional data, how to manipulate Sorry I am a new spreadsheet user, actually I never used Excel before, I jump directly to Calc, and just started learned some basic spreadsheet conecept/operation yet. I found myself easily come across 3-dimensional data which I do not know how to present / calculate. E.g. for our service/client/hour data it has 3 dimensions: 1) the staff who provide the service; 2) the time duration of the service; 3) the client who receives the service; So it's very easy to make such tables out of the data: Table A: each staff's time of service, count by week. Columns are week numbers and rows are staff name; Table B: each client's time of service, count by week. Columns are week numbers and rows are client names; Table C: one emoplyee's performance table. Columns are week numbers and rows are client names. the only difference between B and C is B counts total working our for each client during each period (from different empolyee) but C only shows the service provided by one single emploee. Now I can create table A, B, and C, but not able to connect them. In order to have an detailed data file now I have a Calc file with 40 sheets, 38 sheets are table C for our 38 employees (sheet name is the person's name) and one sheet for table B and one sheet for table A. This structure is not optimized I guess, I am stacking up 38 tables of very similiar structure and the rest 2 tables are just sum of previous 38 tables. If I adjust sheet number 18, I have to make change to table 39 and 40 as well. I know this question look pretty much like a common question that cannot be answered by one or two words, perhaps all I need is a good tutorial (or better, a specific article). However I do not know what this kind if data is, so that I cannot use google for not knowing what keyword to use. Help very much appreciated.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Fri Dec 02, 2005 6:37 am    Post subject: Re: newbie: 3-dimensional data, how to manipulate

 zhangweiwu wrote: This structure is not optimized I guess, I am stacking up 38 tables of very similiar structure and the rest 2 tables are just sum of previous 38 tables. I know this question look pretty much like a common question that cannot be answered by one or two words...

I'd suggest that you might need a ....

Relational database. [Two words ]

It was invented and designed specifically to determine lists of related items and then to interconnect them, and then to present data in a form of your own design from that available related data.

David.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Fri Dec 02, 2005 10:54 am    Post subject:

 Quote: Table C: one emoplyee's performance table. Columns are week numbers and rows are client names. the only difference between B and C is B counts total working our for each client during each period (from different empolyee) but C only shows the service provided by one single emploee.

Your table C looks like this?
Weeks->1 2 3 4 5 6 ....
Client1
Client2 hours
Client3
Client4
sstrider
General User

Joined: 30 Nov 2005
Posts: 40
Location: London UK

 Posted: Fri Dec 02, 2005 3:55 pm    Post subject: Nested Lookups I think some of the principles illustrated in http://www.ridj.biz/OOOsamples/ may help ... sstrider
zhangweiwu
OOo Enthusiast

Joined: 19 Jan 2005
Posts: 132

Posted: Sat Dec 03, 2005 12:01 am    Post subject:

Villeroy wrote:
 Quote: Table C: one emoplyee's performance table. Columns are week numbers and rows are client names. the only difference between B and C is B counts total working our for each client during each period (from different empolyee) but C only shows the service provided by one single emploee.

Your table C looks like this?
Weeks->1 2 3 4 5 6 ....
Client1
Client2 hours
Client3
Client4

Yes, Exactly! And there are 38 table Cs in order to present 38 employee data.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Sat Dec 03, 2005 4:56 am    Post subject:

This has become quite lenghty, because it tries to introduce a concept known as "normalized tables"
Well, your table-design does the second step before doing the first one.
I fully aggree with David: Always save critical data about business, health or scientific results in a database, but a database could not handle your table design neither.
If you use an asian version of OOo, forgive my total ignorance about asian calendars and date-formats, please. I assume some english version of OOo2 using british date format (30/12/05).
Anyway, let's create a spreadsheet as a non-relational database with a row limit of 2^256.
Open a new spreadsheet-doc having Sheet1, Sheet2 and Sheet3.
Every field (column) describes a property of a single entity.
First Row of Sheet1 is a header-row, each following row descibes properties of entities "Person":
 Code: Person  Date  Hours Charles 1/12/05  5 Olli    1/12/05  5 Stan    2/12/05  9 Buster 1/12/05   3 Buster 30/11/05  8

This kind of raw input data, could be used by "some real database".
Notice some input-features, provided by Calc:
If Tools > CellContent > auto-input is switched on, you don't have to type the full name of each person.
In the date column you can enter "1/", meaning the first day of this month or "30/11", meaning the Nov, 30th of this year.
Assuming the three columns above, we derive some calculated fields from our three input columns:
D2:D6> =YEAR('Date')
E2:E6> =YEAR('Date')&"-"& TEXT(MONTH('Date');"00")
F2:F6> =YEAR('Date')&"-"& TEXT(WEEK('Date');"00")
Notice: I use column-header name 'Date', could be a reference like \$B2 as well.
For better separation of input and result, let a blank sheet show the aggregation of our spreadsheet-database.
Select Sheet1, columns A to F (selecting the grey A,B,..,F headers above the table, or using Ctrl+Space)
Data > Data Pilot > Call Wizzard
In the wizzard, first push the "more options"-button.
Define output-range as Sheet2.A1 and switch all checkboxes on (don't care about what they mean).
Drag the grey "Hours"-field into the white datafield-container.
Drag "Week" into the rowfield-container.
Drag "Person" into the columnfield-container
Running OOo2, you can drag "Month" and "Year" into a pagefield-container.
After confirmation (OK), you get a sorted, aggregated timesheet on Sheet2, based on the unsorted raw input-data on Sheet1.
You can show working hours by years, by month or by week, or by all
time-categories together, dragging the grey boxes to the first column (hard to
describe, play with it).
I did not take your whole problem into consideration.
If you do not want to use data pilots (also known as "pivot-tables" in Excel and "cross-tables" in other programs):
You can aggregate properties of any entity with spreadsheet-functions like:
=SUMPRODUCT(\$Sheet1.\$A\$2:\$A\$65536="Buster";\$Sheet1.\$F\$2:\$F\$65536="2005-48")
=SUMIF(\$Sheet1.\$F\$2:\$F\$65536;"2005-48";\$C\$2:\$C65536)
With OOo2 we can add another database-like feature:
Put into column A on Sheet3 all unique names, valid for "Persons" of Sheet1.
Select Sheet1.A1:A65536
call Data > Validation
Here you can define Sheet3.A1:Axxx as valid entries for Column A in Sheet1.
Let me know if you need a workaround for OOo1.[/i]
zhangweiwu
OOo Enthusiast

Joined: 19 Jan 2005
Posts: 132

 Posted: Sat Dec 03, 2005 5:30 am    Post subject: certainly a very detailed and very helpful instruction. I think I'll need half a hour to test it out, but I have to say it's really written in a very clear way, and I suppose if you write documents for OOO that would be terrific! And thank you for noticing I am Asian from the avatar.
zhangweiwu
OOo Enthusiast

Joined: 19 Jan 2005
Posts: 132

 Posted: Sat Dec 03, 2005 9:25 pm    Post subject: Thank you very much Now I managed to create all necessary tables using Data Pilot. Thank you very much. Truly it is very useful tool. Sadly that seems nobody knows it. Even pivot-tables: nobody in the office knows it actually, it seems people just go on working with 40 tables happily without knowing these powerful tools.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum