Can you do countif in aws quicksight with partial text?

Question

I am trying to make a calculated field in AWS Quicksight for leads that have closed. However there are multiple closed statuses in use. I am trying to use

distinct_countif

and was wondering if there was a way to group all closed statuses into one - ex.

distinct_countif(id, status like '%Closed%')

Answer 1

You can use the locate and toLower functions to solve this. The calculated field should more or less look like:

distinct_countif({id}, locate(toLower({status}), 'closed') <> 0) 

Let me know if it works!

Documentation:

1. Locate: https://docs.aws.amazon.com/quicksight/latest/user/locate-function.html
2. toLower: https://docs.aws.amazon.com/quicksight/latest/user/toLower-function.html