I have an Interactions and an Investors table. There are vaious interaction types as investors move from cold > warm > diligence, etc. These interaction types are managed via a single-select and piped in via various automations. Two metrics I’m trying to highlight using a number element in the Interface Designer:
Cold → Pitch conversions
Pitch → Diligence conversions
So far, I tried grouping interactions by investor and leveraing rollups in the linked Investors table. Don’t think that’s the way. In my mind this looks like No status (0), Cold (1), Pitch (2), Diligence (3). For any investor, there could be 2 colds, 2 pitches, etc. It’s not always 1 per intercation type.
How might I go about deiving % of total interactions that converted into these two stages?
NB: At this point in the client’s project, major restructuring of the base is not an option.
That’s where I started but couldn’t find a way to eliminate zeros (no corresponding interaction) without adding 5 more formula fields. I then tried the same approach with a rollup but I’m not seeing an aggregation formula that only returns a numeric value if the conditions are met. ARRAYCOMPACT(values) returns a text string. Essentially, I want to arrive at a % filled.
Hmm, okay. Yeah the path you were going down would be the simplest, one count field for each status, sum it all, and then get the percentage, but I take it we don’t want to create ten new fields?
You could do this with just one rollup field for each percentage you wanted to get by getting a count of the number of times the specific status you’re looking for appeared, and then divide that by a
And here’s the formula I used:
( LEN(ARRAYJOIN(values)) - LEN( SUBSTITUTE( ARRAYJOIN(values), "Todo", 'Tod' ) ) ) / COUNTA(values)
The idea is since we want to count the number of times “Todo” appears, we’ll use a
SUBSTITUTE() to replace all instances of “Todo” with “Tod”, reducing the number of characters by 1 for each instance of “Todo” that exists. We then subtract the number of characters from this newly modified string from the number of characters from the original string, giving us the number of times “Todo” was in said string
As such, you’ll need to switch that “Tod”, “Todo” bit to the exact thing you’re trying to count, so an example for your case would be:
( LEN(ARRAYJOIN(values)) - LEN( SUBSTITUTE( ARRAYJOIN(values), "Diligence", 'Diligenc' ) ) ) / COUNTA(values)