topic Worked days issue in QlikView

Worked days issue

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I'm hitting a dead end with coming up with a formula to calculate worked days between two dates(worked days as in days in which the employees had activity).

So far the formula is something like:
End_date - start_date - Count({<name = {'Non-working activity'}>} distinct assign_date)

A non-working activity is like sick leave or paid leave.
So my question is, how do I count the days between end_date and start_date in which no activity was conducted by an employee?

Re: Worked days issue

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:30:13 GMT

Do you have any Flag for Sick and Leave ??

Re: Worked days issue

Anonymous — Tue, 09 Jan 2018 12:31:23 GMT

Non-working activity covers: Sick leave, paid leave and unpaid leave

Re: Worked days issue

sunny_talwar — Tue, 09 Jan 2018 12:32:27 GMT

Would you be able to share some raw data and let us know what you expect to see from that raw data as an output?

Re: Worked days issue

YoussefBelloum — Tue, 09 Jan 2018 12:35:10 GMT

Hi,

you can use the NetworkDays function for not counting the weekends

Re: Worked days issue

Anonymous — Tue, 09 Jan 2018 12:45:28 GMT

Employee_ID	Activity Date	Activity_name
1	01.02.2018	Non-working activity
1	02.02.2018	Non-working activity
1	03.02.2018	Training
1	05.02.2018	Training
1	06.02.2018	Regular work
1	07.02.2018	Regular work

Assuming the Start_Date is 31.01.2018 and End_Date is 08.02.2018 and the above table is the data I'm working with, the output should be:
Workable days: 9 days
Worked days: 4 days(Anything that isn't classified as "Non-working activity" but is an activity, is considered worked)

Non-worked days: 5(31.01.2018,01.02.2018,02.02.2018,04.02.2018 and 08.02.2018)
The weekends are included in workable days(that's why 03.02.2018 is considered a worked day despite the fact that it is during Saturday)

I need a formula to calculated non-worked days, which should be something like Count({<Activity_name = {'Attività non lavorative'}>} distinct Activity Date) + the calendaristic days in which there is no activity by the said employee

Re: Worked days issue

sunny_talwar — Tue, 09 Jan 2018 12:48:33 GMT

I would think that this should work

End_date - start_date - Count({<Activity_name -= {'Non-working activity'}>} distinct assign_date)

Note the minus sign next to Activity_name to count all the dates which are not Non-working activity

Re: Worked days issue

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:54:30 GMT

Please read Excluding values in Set Analysis

Re: Worked days issue

Anonymous — Tue, 09 Jan 2018 13:02:27 GMT

That's it, thanks!