Bex Query to find total number

[Former Member]

Dear Friends,

I have data like below in my Infocube for different years. its flat file loading  DS->DSO->Infocube.

EmpId	Dept	section	Year	Smoking
101	HR	A	Jan-10	No
102	HR	A	Jan-10	Yes
103	HR	B	Jan-10	Yes
104	FI	A	Jan-10	No
105	FI	B	Jan-10	No
102	HR	A	Aug-10	No

My requirement is like below to find out total number of employees who are Smokers and non smokers.

Dept	Year	Tot no of Emp	Tot Emp Smoking	Tot Emp non-smoking
HR	2010	3	2	1
FI	2010	2	0	2
Result		5	2	3

Please give me your valuable input

Thanks

Vijay

[anshu_lilhori · Accepted solution]

This should be easy

1. Make one CKF and put 1 inside it.
2. Drag this CKF in columns panel--Right click on it--drag smoking char in it and restrict it with value = Yes.
3. Repeat the same step for finding non smoking and total employee.

Regards,

AL

[former_member194898]

Hi,

Do like Anshu said.

Remember to set "Calculate result as" "summation"

otherwise you don't get proper values in result rows.

Regards, Leszek