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?
@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)
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.