Employee head count with accumulation issue

[#1]

Hi Guys

I created one table called DeptChange in MS Access, this table recorded the employee list and their working status(on board or adjust or resign).

My purpose is count the accumulative employee number who is still not resigned by one year, for example:

Year 2015: employee A on board, the count number is 1 (only A)

Year 2016: employee B,C,D on board, but C adjusted to the other department, D resigned, the count number is 3 (A+B+C)

Year 2017: employee E,F on board, but E resigned, the count number is 4 (A+B+C+F)

Year 2018: employee F first adjusted to the other department, then he resigned in a short later, the count number is 3 (A+B+C)

Then I write expression as below and tick Full Accumulation option:

Count({<Reason={'Adjust','On Board'}>} Distinct Name) - Count({<Service={'0'}>} Distinct Name)

However, the chart got the wrong number:

The right chart should be:

2015: 1

2017: 4

2018: 5

2019: 6

Does anyone know what's wrong with the expression, I have no idea how to correct it...

Thanks a lot.

[devarasu07]

Hi,

Can you try like below,

Count({$<Reason={'Adjust','On Board'}, Service-={'0'}>} Distinct Name)

also can you share your mock source that would easy to check and help u. Thanks

[#3]

Hi Devarasu

I tested your expression, the count number is not right, I have uploaded the file source in attachment.

Please help to have a check. Thx.

[Anil_Babu_Samineni]

As per your data, you are getting accuracy values only. But, I wonder what are A, B, C, D in your AccDB ??

[#5]

Hi

A,B,C,D equals to employee name in field "Name" in accdb file, like Zhang San, Li si and so on.

[Anil_Babu_Samineni]

I came to know that now, What was the logic behind year 2018 as 5. That i am confused.

[#7]

The logic is:

Before 2018, the total number of employee who is still on board is 4, and in 2018, two new employee was on board, but one of them got resigned in the same year, so the total number in 2018 is 5.

If the employee on board and resign in the same year, I will not count in that year.

[Anil_Babu_Samineni]

Like this?

[#9]

Seems the chart is right, your expression is?

[Anil_Babu_Samineni]

I was thinking and got some idea to change field Year in script level

Year(Date) as Year

Year is dimension

And expression would be this? Rather change Date to Year

If(Date <= Year(Today()),

Count({<Date = {"$(=Year(Today()))"}, Reason={'Adjust','On Board'}>} DISTINCT Name), RangeCount(Below(Count({<Reason={'Adjust','On Board'}>}Name),0),Above(Name,1, RowNo(TOTAL))))

OR

If(Date <= Year(Today()),

Count({<Date = {"$('<=' & =Year(Today()))"}, Reason = P({<Reason = {'Adjust','On Board'}>}Reason), Reason = E({<Service = {'0'}, Reason = {'Resign'}>}Reason)>} DISTINCT Name),

RangeCount(Below(Count({<Reason = P({<Reason = {'Adjust','On Board'}>}Reason), Reason = E({<Service = {'0'}, Reason = {'Resign'}>}Reason)>} DISTINCT Name),0),Above(Name,1, RowNo(TOTAL))))

Still facing, issue please let us know