Spotfire - Difference of IDs over years

Question

I have a dataset like this: a 3 year employees list with some employees that are present in all three years (ID:1,2,3), some that joined/left the company in different years.

Year IDEmployee
2014 1
2014 2
2014 3
2014 4
2015 1
2015 2
2015 3
2015 5
2015 6
2016 1
2016 2
2016 3
2016 6
2016 7
2016 8

I need to calculate the in/out fluxes for all the periods (2015 vs 2014 and 2016 vs 2015)

• "In" flux = Number of employees in N+1 year, not present in N year
• "Out" flux = Number of employees in N year, not present in N+1 year

In this case the result is:
- for 2015 - In flux: 2 (ID:5,6 that were not present in the previous year). Out flux: 1 (ID:4 that was present in the previous year but now not present)
- For 2016 - In flux: 2 (ID:7,8 that were not present in the previous year). Out flux: 1 (ID:5 that was present in the previous year but now not present)

The simplest way to do it should be using a calculated column, but I cannot write it:

InFlux column: UniqueCount([IDEmployee]) OVER ([Year]) - UniqueCount([IDEmployee]) OVER (PreviousPeriod([Year]))
OutFlux column: UniqueCount([IDEmployee]) OVER ([Year]) - UniqueCount([IDEmployee]) OVER (NextPeriod([Year]))

Any suggestion?
Many thanks, Lollone

Answer 1

@Lollone- Please check the below solution and let me know if it works for all possible scenarios.

In-flux column:

If(Count([ID]) over (intersect(previousperiod([Year]),[ID]))=0,"In")

Out-flux column:

If(([Year]!=Max([Year])) and (Count([ID]) over (intersect(nextperiod([Year]),[ID]))=0),"Out")

Final output: