COUNT IF

Vince

Hi guys,
Any recommendation on approaching the equivalent to the excel formula:
COUNTIF(range_of_numbers,">5")/COUNT(range_of_numbers)

Goal: count the % of numbers about the value 5

Here's my current approach
Using library addon the math lib are limited to count. 
• So I'd need to do an add-on search WHERE range_of_numbers>5 only, then do the COUNT of that.
• Then another add-one search with all range_of_numbers, then do the COUNT of that.
• Then finally use both variables in my API call to then update a variable to do the division math, to get the final percentage (or do it frontend).


Would there be a better/simpler way?

Thanks!

Ray Deck

Are you talking about an arbitrary list of numbers, or is this specific to working with a database query - maybe a screenshot will clear up the ask?

The short answer is that "countif" is not a built-in function but you can recreate the logic of it in the function stack, or as a custom function (my preferred solution). But what that functionality would do depends a bit on your inputs - sharing your situation would let me do better by you! 

Vince

Hi Ray! Thanks for your response!

Database has records with a NPS score (recommendation ratings). Each record is a separate Rating for a specific thing, such as a hotel.
I'm calling all records for Hotel X, and where the recommendation rating is > 5. Finally I want to transform that into the % of people who would recommend the hotel, because they gave a more than neutral or negative rating.
This needs to thus be in a GET API call. 

Hope that makes sense? 

Ray Deck

Got it! You can separate the question of what's in the GET call from the underlying logic. That's where the power of the function stack comes in. In your case, I'd consider running an additional query in your function stack of reviews > 5 and aggregating those responses to get your happy count,  and then merging the data back into the result of your first query to return your super-powered result by looping over the array to merge together. (I've discussed how to merge two arrays with a common key: https://www.loom.com/share/31aa1e09f29f4c3fa4ee0981de95e9bd?sharedAppSource=personal_library)

All this adds a little ceremony, but it's completely do-able. LMK if you have questions!