# COUNTFILTERED Returned Value Expression in Analytics Editor

Hi Tulip Team,

I was wondering if a COUNTFILTERED expression is being considered for the Analytics Editor?

I have an application where I am recording machine state and density every 60 seconds to a table. I want to count the number of times a density value is non-zero during a productive state and use that as part of an OEE calculation.

There’s no COUNTFILTERED expression to do this easily. I was able to work around this by adding another field to the table for Density > 0 and populating it with a 1 or 0. Then I used the SUMFILTERED expression on that field and filtered it by productive state only.

Just wondering if there’s a plan to introduce a COUNTFILTERED expression in the future?

Thanks,

-Steve

@steve.midcalf It’s hiding in plain sight! The COUNT() function does exactly what you are asking for. It can take no inputs or a boolean statement.

@danielpomeranz The COUNT function can only take 0 or 1 arguments, SUMFILTERED takes 2 arguments. What I’m asking for is a COUNTFILTERED that can take 2 arguments.

The expression I am using: SUMFILTERED(@Table Density Over Zero Column, @Table Machine State = 6)

I had to add a new Density Over Zero Column and set it to a 1 anytime the Density is greater than zero. So now I can use the SUMFILTERED expression to add up the number of rows where this is the case and then filter it to only those rows where Machine State = 6.

I can use COUNT(@Table Density Column > 0) to get the total number of rows where the Density is greater than zero, but I cannot filter this by only those rows where Machine State = 6

The expression I want to use is this: COUNTFILTERED(@Table Density Column > 0, @Table Machine State =6)

Ahh, I think you can do COUNT(@Table Density Column > 0 & @Table Machine State = 6). If that doesn’t work try using two &s.

1 Like

@danielpomeranz That worked! Thanks

1 Like

Thanks @danielpomeranz for the great help here! Glad this works for your application @steve.midcalf

I am not sure how clear this is in the knowledge base (@Beth) but it’s also worth documenting for whoever reads this that you can use | as an “or” clause. So COUNT(@Table Density Column > 0 | @Table Machine State = 6) would give a count where either statement is true.

1 Like