Plan Vs. Actual

[wossenhh]

Hi QlikViewers,

I need some help with the following two tables. One has data for Plan and the other one is for Actual.

John has moved from his previous department (Sales) and start working in a new department (Marketing). He has worked couple of hours for Client 'A' before and after he moved to the new department. The same scenario, for Marry who got promoted in her current department (Finance) from Controller to Controller Supervisor and she had some hours per her new and old titles as shown the tables below.

Actual Table

Client	Dep	Title	Employee	EmpID	Hours
A	Sales	Sales Associates	John	101	129.50
A	Marketing	Marketing Manager	John	101	11.50
B	Finance	Controller	Marry	200	215
B	Finance	Controller Supervisor	Marry	200	310.50

Plan Table

EmpID	Dep	Name	Title	Status	A	B
101	Sales	John	Sales Associate	Inactive	450	0
101	Marketing	John	Marketing Manager	Active	0	0
200	Finance	Marry	Controller	Inactive	0	10
200	Finance	Marry	Controller Supervisor	Active	0	15

Now I want to create Plan Vs Actual table that shows the old & new titles and departments for all employees who put some hours for Client A, B and so on. Here is the table that I want to create.

Dep	Title	Employee	Headcount	Plan A	Actual A	Plan B	Actual B
Sales	Sales Associate	John	0	450	129.50	0	0
Marketing	Marketing Manager	John	1	0	11.50	0	0
Finance	Controller	Marry	0	0	0	10	215
Finance	Controller Supervisor	Marry	1	0	0	15	310.50

Hope it is clear.

Thanks for your help in advance.

Wossen

[buzzy996 · Accepted solution]

use this,

[wossenhh]

Thanks Shiva! Almost there, but the headcount should be 2 for John and Marry, not 4. It should be 1 for Active status and zero for Inactive.

[petter]

Something like this?

[buzzy996]

Just repalce headcount expression with this: =If(Status='Active',count(EmpID),'0')

else PFA