topic Re: Analysis based on Dept and Individual in QlikView

Analysis based on Dept and Individual

Mon, 20 Jan 2014 18:10:32 GMT

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

Nicole-Smith — Mon, 20 Jan 2014 18:15:59 GMT

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

Re: Analysis based on Dept and Individual

Anonymous — Mon, 20 Jan 2014 18:20:29 GMT

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

Re: Analysis based on Dept and Individual

Mon, 20 Jan 2014 18:53:53 GMT

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

Nicole-Smith — Mon, 20 Jan 2014 19:00:22 GMT

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

Mon, 20 Jan 2014 19:16:41 GMT

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

Nicole-Smith — Mon, 20 Jan 2014 19:40:15 GMT

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.

Re: Analysis based on Dept and Individual

Mon, 20 Jan 2014 19:55:28 GMT

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

Nicole-Smith — Mon, 20 Jan 2014 20:19:54 GMT

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

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