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!!! :-)
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.
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
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?
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.
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!!
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!!
No idea of the dimensional syntax, but it would look something like:
Start_date <= end_of_year and end_date >=start_of_year
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?
No, really it is just
Start_date <= end_of_year and end_date >=start_of_year
Think about it.
Okay, but what should I put in the "Expression" property? Thanks!