Count unique?

[David_Hahn]

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.

[Suvrutt_Gurjar]

Could you please try?

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

[David_Hahn]

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…

[Suvrutt_Gurjar]

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

help.appsheet.com

UNIQUE()

Unique items in list

[David_Hahn]

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!

[Suvrutt_Gurjar]

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

Guide on How To Create a Community Post Tips & Tricks 💡

Dear valuable members and users, For a long time I’m seeing a lot of multiple and concurrent posts, posts like novels seeming to aim the Pulitzer Price, exactly the same questions by the same user but paraphrased/re-phrased etc. Being a dedicated community member and an AppSheet developer who is reading around 2.5K posts per day, I urged myself to tip on some good advice and best practice BEFORE posting something in the community: #1 - APPSHEET DOCUMENTATION AppSheet has a very good help do…

[fon1 · Accepted solution]

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

[David_Hahn]

Thank you! This works like a charm!