Count unique?

Hello.

I have a clock in/clock out app and I am trying to figure out how many days in a month employee worked. I used COUNT formula, but now I figured, if an employee clocks in twice or three times a day - my formula counts this as +1 working day.

Example: * 2021-5-21 - employee clocks in and out - this is 1 working day in my formula
* 2021-5-22 - same employee clocks in and out twice this day- my formula counts that as 2 working days. But it should be one working day.

This is how my formula looks now:

COUNT(
SELECT(
MASTER[Date Out 2],
AND(
([YEAR] = [_THISROW].[YEAR EMP]),
([MONTH] = [MONTH EMP]),
([Employee Name]=[Name].[Employee Name])
)
)
)

I thank you in advance for the help.

Could you please try?

COUNT(
SELECT(
MASTER[Date Out 2],
AND(
([YEAR] = [_THISROW].[YEAR EMP]),
([MONTH] = [MONTH EMP]),
([Employee Name]=[Name].[Employee Name])
), TRUE
)
)

1 Like

Appreciate your help, but no luck, still counting those records from same employee who clocked in many times in a same day. It adds all those ‘clokckins’ to working days.

If I could somehow incorporate unique in this fornula…

May I know what results you are getting with the revised formula?

Also does below help?

COUNT(UNIQUE(
SELECT(
MASTER[Date Out 2],
AND(
([YEAR] = [_THISROW].[YEAR EMP]),
([MONTH] = [MONTH EMP]),
([Employee Name]=[Name].[Employee Name])
)
)
)
)

2 Likes

Great! It works! I figured, I made a mistake in my formula. I was counting ‘Date Out’ unique values instead of ‘Date In’.

Using previous solution now works as well.

COUNT(
SELECT(
MASTER[Date In 2],
AND(
([YEAR] = [_THISROW].[YEAR EMP]),
([MONTH] = [MONTH EMP]),
([Employee Name]=[Name].[Employee Name])
), TRUE
)
)

Thank you, sir, much appreciated!

2 Likes

You are welcome. All the best with your app.

You will find many useful help articles, sample apps at the below link. Just search by the key words such as “Unique, Select” … etc.

https://www.appsheet.com/Support?q=&hPP=10&idx=help&p=0&is_v=1

The below post also has many useful references

3 Likes

ISBLANK( FILTER( “Time stamp”, AND( ISNOTBLANK([employee ID]), ([employee ID] = [_THISROW].[employee ID]), ISNOTBLANK([name-surname]), ([name-surname] = [_THISROW].[Name-Last Name]), ISNOTBLANK([Entry Date]), ([Entrance Date] = [_THISROW].[Enter Date]) ) ) )

I use this formula is to record only once a day

3 Likes

Thank you! This works like a charm!

1 Like