topic Re: Employee head count with accumulation issue in QlikView

Employee head count with accumulation issue

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Employee head count with accumulation issue

devarasu07 — Mon, 10 Jul 2017 02:18:18 GMT

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

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 02:25:17 GMT

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.

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 03:25:48 GMT

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

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 03:30:37 GMT

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

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 03:31:46 GMT

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

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 03:40:37 GMT

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.

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 03:52:05 GMT

Like this?

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 04:01:03 GMT

Seems the chart is right, your expression is?

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 04:15:08 GMT

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

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

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 04:17:24 GMT

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

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 05:18:25 GMT

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?

Re: Employee head count with accumulation issue

Anil_Babu_Samineni — Mon, 10 Jul 2017 06:22:24 GMT

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?

Re: Employee head count with accumulation issue

Mon, 10 Jul 2017 08:58:47 GMT

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.