8 Replies Latest reply: Jan 20, 2014 3:19 PM by Nicole Smith

Analysis based on Dept and Individual

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

• Re: Analysis based on Dept and Individual

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

• Re: Analysis based on Dept and Individual

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)

• Re: Re: Analysis based on Dept and Individual

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

• Re: Analysis based on Dept and Individual

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

• Re: Re: Analysis based on Dept and Individual

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

Data:

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)

Month,

sum(values) as [SumOfDept]

RESIDENT Data

GROUP BY Dept_id, Month;

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

• Re: Analysis based on Dept and Individual

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.

• Re: Analysis based on Dept and Individual

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

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