Worked days issue

[Anonymous]

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?

[Anil_Babu_Samineni]

Do you have any Flag for Sick and Leave ??

[Anonymous]

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

[sunny_talwar]

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?

[YoussefBelloum]

Hi,

you can use the NetworkDays function for not counting the weekends

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

[Anil_Babu_Samineni]

Please read Excluding values in Set Analysis

[Anonymous]

That's it, thanks!