Announcements
This site is in read only until July 22 as we migrate to a new platform; refer to this community post for more details.

Help With Count and multi criteria Select statement

I developed an attendance based on a point system.  If the am employee has been absent more than 3 times for the same reason, in this case, "Ausencia Justificada/(Excused Absence) then the employee starts accumulating 2 points each time they're absent for the same reason.  For some reason, my formula is not working like I want it to.  Help, please.

My formula

IF(Count(SELECT(Form[Status],AND(
[_THISROW].[Account #]=[Account #],
[Status]="Ausencia Justificada/(Excused Absence)"
)))<=3,0,
[Status].[Points])

Solved Solved
0 7 395
1 ACCEPTED SOLUTION

It sounds like there is nothing wrong in your original formula othrr than this part "<=3". When the person adds the 4th record, and there is 3 already, that's why it gives 0. Remember that formula doesn't read the 4th one while the person is filling it and it's not saved yet. That's why you need to use "<3".

View solution in original post

7 REPLIES 7

In what way it's not working?

@AleksiAlkio 

As you can see below.  It's accumulating points on the 5th incident when it should be doing it on the 4th.  

Mauricio_Bick_0-1698408852656.png

 

Any ideas @AleksiAlkio ?

It sounds like there is nothing wrong in your original formula othrr than this part "<=3". When the person adds the 4th record, and there is 3 already, that's why it gives 0. Remember that formula doesn't read the 4th one while the person is filling it and it's not saved yet. That's why you need to use "<3".

I was over thinking it.  Thank you @AleksiAlkio !

You're welcome!

It seems to be working now but it feels clunky.  I feel like there's a better solution.  This is what I did.

I created a VC using this expression 

Count(SELECT(Form[Status],and(([Status]="Ausencia Justificada/(Excused Absence)"),[_THISROW].[Account #]=[Account #])))

and in my Points column, I used this other expression

IF(AND([Status]="Ausencia Justificada/(Excused Absence)",
[Excessive Excused Absence]<=2),0,[Status].[Points])

 

 

Top Labels in this Space