I have a rather long spreadsheet with historical information of issues solved by some user on a collaborative environment.
The spreadsheet have the following (relevant) columns
date, week no., project, author id, etc...
The week no. is calculated from the date, is basically the year concatenated with the week number within that year; for instance, both 2009-02-18 and 2009-02-20 yield the week number 200908 - the 8th week of year 2009; and 2009-02-23 yields 200909 - the 9th week of year 2009.
I need to count how many different users (given by author id) contributed to some project, on a weekly basis.
I have setup a data pilot with the week as Row Field, the project as the Column Field, and count-author as the Data Field. However, this counts the author id as different instances. This is not what I need. I need to count how many different users contributed to each project on a weekly basis.
I expect to get something like:
projects week Project1 Project2 Project3 200901 10 2 200902 2 7
Each inner cell containing how many different users contributed. With the count-author configuration, what I get is how many contributions (total) got the project on that week.
Is there a way to tell OpenOffice Calc to do what I want?
UPDATE: Following the suggestion given by other users, this question is now posted on Super User: http://superuser.com/questions/232339/openoffice-calc-how-can-i-count-the-number-of-different-items-with-data-pilot