Hi all,
I have a two-table base. The first table/screenshot below is for survey responses; each record is an individual survey response. The second table/screenshot is where the responses roll up for each survey.
Is there a way within the rollup filed formula to achieve my highlighted desired result? I'm trying to avoid adding multiple rollup fields for each response type and then concatenating all of the information in a new column.
Hi @Jill_Pierce,
This is what it looks like when realized in a single Rollup field.
"Every day : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|","")) & " responses)\n" &
"Once a week : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|","")) & " responses)\n" &
"Never : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|","")) & " responses)\n"
Hi @Jill_Pierce,
This is what it looks like when realized in a single Rollup field.
"Every day : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|","")) & " responses)\n" &
"Once a week : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|","")) & " responses)\n" &
"Never : "&
ROUND(
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|",""))/
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|","")) & " responses)\n"
@Sho Thank you for taking the time to write that formula! Do you have an opinion on whether the single formula within the rollup field would work best or, use several rollup fields on the summary table?
@Sho Thank you for taking the time to write that formula! Do you have an opinion on whether the single formula within the rollup field would work best or, use several rollup fields on the summary table?
If you don't plan to use the several rollup field values elsewhere, I think a single rollup field is fine.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.