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.

[Anil_Babu_Samineni]

Explanation - This expression gets the Till current year normal count of name with filters and then Accumulation further years. That is where i am thinking as you required

[#3]

I tried your expression:

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))))

and

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))))

However, the number is not correct,

Did I miss something?

[Anil_Babu_Samineni]

Would you mine, Can you write Year rather Date for me once. Let me open your application if i got chance

Xu Ran wrote:

I tried your expression:

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

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

-----------------------------------------------------------------------------

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

Count({<Year = {"$('<=' & =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))))

Did I miss something?

[#5]

I changed Date to Year as you said, but the chart still got the wrong number, you can check the latest attachment in my reply.