Using Regexp_Count

REGEXP_COUNT is quite interesting, especially when you combine it with NS_CONCAT. Doing so will allow you to count the number of elements in the NS_CONCAT results, allowing for further flexibility to your searches.

Further, you can perform mathematical operations between multiple REGEXP_COUNT expressions. You'd normally think to create a calculated measure in the Analytics Workbook in order to perform this, but it appears there are times when a saved search is all you need. Here's an example:

Say you want the search to show each sales rep, and the number of customers they have that are from referrals (marked via referral checkbox in the customer record). Sales orders, and phone calls have the same checkbox, and you want to count these too. To add, you have another checkbox, that indicates if an upsell was offered. The checkbox appears in sales order, call, and task records. Then you'd also want to count each instance the checkbox is marked.

Using NS_CONCAT(DISTINCT) will return each internal id of matching records. Then if you append a letter to each internal ID, say 'A', you will be able to use REGEXP_COUNT to count each instance of 'A'. Thus giving you an accurate count of elements, without having to deal with cubes or tesseracts caused by multiple record types, and/or complicated case whens.

The last formula on the screenshot above shows a total of each preceding formula. I simply copy/pasted the formulas together, delimiting them with '+'.