Skip to main content
Solved

Concatenate and summarize each rollup value

  • November 29, 2023
  • 3 replies
  • 48 views
Jill_Pierce
Forum|alt.badge.img+6

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. 

 

 

 

Best answer by Sho

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"

 

 

 

3 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • Answer
  • November 30, 2023

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"

 

 

 

Jill_Pierce
Jill_Pierce
Forum|alt.badge.img+6
  • Jill_Pierce
  • Author
  • New Participant
  • November 30, 2023

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? 

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • December 1, 2023

@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.

Terms & ConditionsAccessibility statement