Hi all,
I am working on the data containing these information
and I would like to calculate the number of worked days by each employees.
To calculate this, I have a formula as below:
Working days:
IF not(isnull([1st Time]) or isnull([2nd Time])) and
ISNULL([Lve.]) and [Abs?] = 'No'
then 1 else 0 END
Working Missing Clock:
IF not(isnull([1st Time]) and isnull([2nd Time])) and
[Abs?] = 'No' and ISNULL([Lve.]) and
[Excp.] = 'Missing Clock In/Out'
then 1 else 0 END
Working:
[working_days] + [working_missing_clock]
Working is what i want to calculate here, by right, by each employee, it should not be more than 30 (as we have 30 days in June) but the numbers that i have when i put in the measure is sometimes >100, which does not make sense.
I have used the LOD function: {FIXED [Employee #} : sum (Working)} to remove the duplicate but nothing's changed.
Btw, working on a highly confidential data, therefore, i am unable to share my workbook here. Apologies in advance.
Appreciate your help on this!
It's a bit difficult to troubleshoot without an example workbook but it looks like the 'Working Days' calculation will also include the days that are 'Working Missing Clock', so the 'Working' field shouldn't need to include both of them, as it will count any days that are missing clock as double.
It also looks like there are duplicate lines in your data, so each one will count twice (or four times if they are missing clock). If this isn't something that can be cleaned either in the data or at the point of import then you might be better using a working calculation that will give the date when it hits the criteria, and then count the distinct of days rather than summing.
