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:

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?

  • I'm not sure how the source looks. For each week/project/user-id combination there is a separate line? It would be helpful to see a short sample too. Comments, however, are not useful, to show what I mean. :) – user unknown Apr 7 '11 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.