OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

newbie: 3-dimensional data, how to manipulate

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
zhangweiwu
OOo Enthusiast
OOo Enthusiast


Joined: 19 Jan 2005
Posts: 132

PostPosted: Fri Dec 02, 2005 4:52 am    Post subject: newbie: 3-dimensional data, how to manipulate Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

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

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 Smile ]

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Dec 02, 2005 10:54 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
sstrider
General User
General User


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

PostPosted: Fri Dec 02, 2005 3:55 pm    Post subject: Nested Lookups Reply with quote

I think some of the principles illustrated in http://www.ridj.biz/OOOsamples/ may help ...


sstrider
Back to top
View user's profile Send private message Visit poster's website
zhangweiwu
OOo Enthusiast
OOo Enthusiast


Joined: 19 Jan 2005
Posts: 132

PostPosted: Sat Dec 03, 2005 12:01 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Dec 03, 2005 4:56 am    Post subject: Reply with quote

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:
Add header Year in D1
D2:D6> =YEAR('Date')
Add header Month in E1
E2:E6> =YEAR('Date')&"-"& TEXT(MONTH('Date');"00")
Add header Week in F1
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]
Back to top
View user's profile Send private message
zhangweiwu
OOo Enthusiast
OOo Enthusiast


Joined: 19 Jan 2005
Posts: 132

PostPosted: Sat Dec 03, 2005 5:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
zhangweiwu
OOo Enthusiast
OOo Enthusiast


Joined: 19 Jan 2005
Posts: 132

PostPosted: Sat Dec 03, 2005 9:25 pm    Post subject: Thank you very much Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group