Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
use this,
use this,
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.
Something like this?
Just repalce headcount expression with this: =If(Status='Active',count(EmpID),'0')
else PFA