Set Analysis inside a dimension ? or any good idea ?

[#1]

We would like to create a table (matrice) who show the employee transfers inside a company.

The idea is to have the departement as lines (1.1.2010) and colums (31.12.2010) inside the matrice.

The diagonal will show the number of employee still in the same department, and outside the diagonal, we will see the transfers.

We have tried to use a Set Analysis inside the dimension window, but I bet it's not the right way.

We would like to avoid scritps modification if possible.

And the use of the "if" is not easy in this case.

Here is the example:

Situation 1.1.2010

EmployeeID    Sector
---------------------------------
Num1          Dep1
Num2          Dep1
Num3          Dep2
Num4          Dep2
Num5          Dep2

Situation 31.12.2010
EmployeeID    Sector

----------------------------------
Num1          Dep1
Num2          Dep1
Num3          Dep1
Num5          Dep2

(Num4  left the system)

Transfer Matrice (what we would like to obtain)
---------------------------------------------------------------------

Employee hired the first day of the year and the last year. The new employees and the employees who left the company are unaccounted.

Number of Employee:

                           2011

2010             Dep1   Dep2 

Dep1                 2      0   

Dep2                 1      1

Interpretation of results
--------------------------------------

Two employees stay in Dep1 during the year 2010
One employee move from Dep2 to Dep1 during the year 2010
One employee stay in Dep2 during the year 2010

(one employee is out of the system during the year)

Thank you for your suggestions

[sbaldwin · Accepted solution]

Hi this is like base churn which i found when using SQL it was best to split the two sets (in qlikviews case load as different tables and then join back on the employee, this is what i did in the Churn.qvw by modifying the script  but have also created one which just uses set analysis this is Churn2.qvw which seems to do the job.

Thanks

Steve

[#3]

Thanks a lot for your help. The second soluition is very good.

Do you have any suggestion how to use directly the Dep1 and Dep2 coming from the dimension inside the expression instead of declaring each Sector (one per line).

Is the GetFieldSelections useful in this case ? Can we dynamically create separates expressions using fields of dimension ?