Urgent help needed with a simple distribution report!

[superlative]

I am trying to do a distribution type report:
                                             2011                                                 2012
                           1        2        3        4         5               1        2        3        4         5
Department1       10%    20%  50%   15%     5%           
Department2

... where 1,2,3,4,5 is the performance rating distribution for the department in question for the given year.  The problem is that I can easily get the number of people in "Department1" who have "rating 1" or "rating 2", but I can't insert the total number of people in "Department 1" into the expression.  It keeps filtering the value by "rating 1".

The formula for a given rating and department = number of people with that rating / total number of people in that department

Please help as I am under a tight deadline!  Thanks!!! :-)

[superlative]

example: if the number of people in Department1 with a rating of "1" is 15, and the total number of people in Department1 is 80, then I want to see the value 15 / 80 = 18.75%.

What I end up getting in that cell is 15 / 15 = 100%.  I can't insert the number "80" in there, unless I create a separate "total for department" column next to the "5" column.  But of course I don't want the "80" value in a separate column, I want it as part of the distribution formula.

I tried googling "distribution report", but all I get are "email distribution list" reports in search results.

[BigChris]

I could be missing something in what you're looking for, but I think you need something along the lines of:

count([people] for [rating],[department]) / count([people] for [department])

I might be overstating it depending on what groupings you've got, but that should work.

C

[superlative]

Thanks!  I only have a basic understanding of Cognos, hence the seemingly basic question.

Question: should I include the date field in the formula?

ie.:

count([people] for [rating],[department],[date]) / count([people] for [department],[date])

so that I'm only getting the count for the year?

[BigChris]

Sorry, I missed that, yes you will probably need to put that in as well, otherwise you'll get the counts as a total for both years.

[superlative]

How about this:

Each employee has a start date and end date indicating when they were active.

I only want to show employees active for the year in question.

For example if their start date was 2010-Aug-01 and their end date was 2011-Sep-01 then their stats should appear in year 2010 and 2011 but not 2012.  How do I do this?  Thanks!!

[superlative]

Can someone please help with the problem?

Here it is again:

I have an employee with a start date and and end date in which they were active.

I only want to show employees who were active in say "2011".  What is the expression that I should use for a tabular report?  Thanks!!

[calson33]

No idea of the dimensional syntax, but it would look something like:

Start_date <= end_of_year and end_date >=start_of_year

[superlative]

Actually it's more like:

Employee_Active_Start <= Year_start and Employee_Active_End <= Year_End
OR
Employee_Active_Start >= Year_start and Employee_Active_End <= Year_End
OR
Employee_Active_Start >= Year_start and Employee_Active_End > Year_End
OR
Employee_Active_Start <= Year_start and Employee_Active_End > Year_End

What's the Expression to use?

[calson33]

No, really it is just
Start_date <= end_of_year and end_date >=start_of_year

Think about it.

[superlative]

Okay, but what should I put in the "Expression" property?  Thanks!