Employee in one set but not the other

[anagharao]

Hi

I have a set of employee data and changes over weeks.

BU     EmployeeID           FTE              As of Date

1          1                              1                    12/31/2016

2          2                              0.5                 12/31/2016

3          3                              1                    12/31/2016

3          4                              0.25               12/31/2016

1          1                              1                    4/1/2017

2          3                              1                    4/1/2017

3          4                              0.25               4/1/2017

3          5                              1                    4/1/2017

2          6                              0.5                 4/1/2017

How do i find the sum of FTE where in the employee is there in one set (12/31/2016) but not in another (4/1/2017) and visa versa.

also, I need the expression to handle any two dates.

Regards.

Anagha

[sunny_talwar]

The two dates will be selected or entered as a input by user? Also, where are you looking to show these employees? In a text box object or in a table??

[anagharao]

Ideally the two dates are to be picked form lists, and the result shown in a table

[anagharao]

The end result should be something like this:

          Opening           Addition         Deduction          Closing

BU

1

2

3

4

Opening : as of 12/31/2016

Addition : in 4/1/2017 and not in 12/31/2016

Deduction: in 12/31/2016 and not in 4/1/2017

Closing: 4/1/2017

[anagharao]

I tired the below with hardcoded date to find addition (in 4/1/2017 only). but the results are blank

IF(AGGR(CONCAT({<[As of Date]={'12/31/2016','4/1/2017'}>}[As of Date]),Emplid)='4/1/2017', SUM({<[As of Date]={'4/1/2017'}>}FTE))

I need help.

[uacg0009]

Hi Anagha,

Please see the attachment.

I think there is more than a way to make that. My method is using alternate state.

Thanks.

Aiolos