Analysis based on Dept and Individual

[#1]

How can i show a chart which displays the values based on a individual and also the department.

Ex - Input dataset

emp_id	dept_id	values
1	d1	2
2	d1	3
3	d2	5
4	d2	4
5	d2	8

Needs to show a chart by comparing the individual values vs the Dept Total values

emp_id	dept_id	values	SumDeptToal
1	d1	2	5
2	d1	3	5
3	d2	5	17
4	d2	4	17
5	d2	8	17

The Dept is just one dimension, there are other dimensions like Month, Year which comes in to account when they select an individual.

Calculating these values at the table level is not possible.

Could be a simple thing but i think i am missing some thing, i tried the Set Analysis as below but didn't work

sum ({1<EMP_id={"*"}>}values)

Any ideas would be greatly appriciated .

Thanks,

Rajesh

[Nicole-Smith]

I believe the attached gives you what you're looking for.

[Anonymous]

Nicole Smith beat me to it.  Rajesh Kumar Bodla, see what Nicole did with Sum(TOTAL <dim> field_name).

[#4]

Thanks Nicole and Jeffrey.

Thats great, the total is working for Dept_id, but when i add the Month the values are still at the Dept Level, How can get the Total of department at each Month?

Sum( Values) for the entire department (By Month Only)

[Nicole-Smith]

I believe the attached is what you want.  You also need to add Month with the Dept_id in the expression.

[#6]

Excellent , that's what i am looking for, But this is not working when i have a List box and selecting only that Employee?

[Nicole-Smith]

It seems like you have a lot of things dependent on this.  I suggest calculating it in the script:

Data:

LOAD * INLINE [

EMP_id, Dept_id, values,Month

1,d1,2,1,

2,d1,3,1,

3,d2,5,1,

4,d2,4,1,

5,d2,8,1,

1,d1,4,2,

2,d1,6,2,

3,d2,5,2,

4,d2,3,2,

5,d2,8,2,

];

LEFT JOIN (Data)

LOAD Dept_id,

     Month,

     sum(values) as [SumOfDept]

RESIDENT Data

GROUP BY Dept_id, Month;

I have also attached a .qvw with this in it.

[#8]

Nicole, i do appreciate your time

I do understand that this can be done at the Sciprt level but it will be a static for dimensions ( suddenly if i want to add another level like Region) need to change the script.

I just gave the Sum as example for sample dataset. We need to do the Average, Sum, Median besides there are 10-12 different measures which needs these analysis-  trying to see if we can accomplish this at the report level.

[Nicole-Smith]

In that case, you can use set analysis to ignore selections in the EMP_id field:

sum({<EMP_id=>} TOTAL <Dept_id,Month> values)