Sep 21, 2022 05:33 PM
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.
Sep 22, 2022 07:35 AM
Hm, feels like conditional count fields might work for this. May I know what issues you faced when trying to use those?
Sep 22, 2022 02:50 PM
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.
Sep 26, 2022 01:22 AM
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)
Sep 27, 2022 02:25 PM
Thank you! This almost works but the data structure on Table 1 gets in the way. I’m curious about ‘Tod’ in your formula - I’m not sure how that works :slightly_smiling_face: If I switch it to anything else it fails. What’s the underlying logic?
Sep 27, 2022 08:47 PM
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)
Sep 30, 2022 04:04 PM
Brilliant. Thanks for taking the time! Always learning something new here.