This article describes how to concatenate text and numbers into automatically updating strings that can be used for dynamic commentary.
As an example, the text string "21% of Females prefer Coke Zero" could be created by referencing the gender, brand, and the percentage, then using the paste or paste0 function to combine these with "of", and "respondents prefer". A plethora of examples using the paste() and paste0() functions are available in How to Use Paste Functions to Create Dynamic Text Using R.
While the examples below describe how to do this using R code directly in a Calculation, Displayr also offers a user-friendly point-and-click method per How to Use the Dynamic Text Box in Displayr.
Requirements
- A table (The first example includes a crosstab of percentages. The second example includes a crosstab with Column % and Column Sample Size as Cell statistics).
- Two control boxes, one referencing the table rows and the other the columns (for the second example only).
Method - Simple example
In this example, we wish to create an automatically updating text calculation based on our Coke Zero - Female cell from the preferred cola by gender table:
The end result will be the below:
1. From the toolbar, select Calculation > Custom Code.
2. Click onto the page to place the custom calculation.
3. Paste the below under Properties > R CODE in the object inspector:
x = table.Preferred.cola.by.Gender["Coke Zero", "Female"]
paste0(round(x,0), "% of Females prefer Coke Zero")
- Here, we extract the corresponding table cell and define it as x.
- We then use R's paste0 function to paste this value with the string "% of Females prefer Coke Zero".
- Additionally, we round the percentage value to 0 decimal places to tidy the finished result.
As long as the Calculation is set to Automatic, whenever the underlying data changes, the commentary will likewise update:
Method - More complex example
You can also create bulleted lists that update. In this example, we have a table that shows cola preferences per quarter. We also have two controls, one that lists the date column names and one that lists the cola brand row names:
What we want to do is customize commentary based on which date and brand are chosen in the controls. If we select Qtr2 and Coca-Cola in the control boxes, for example, the output will return the below:
1. In the toolbar, select Calculation > Custom Code.
2. Click onto the page to place the custom calculation.
3. Paste the below under Properties > R CODE in the object inspector:
tab = table.Q3.Preferred.cola.by.quarters[,,"Column %"] #Find table
base = table.Q3.Preferred.cola.by.quarters[Brand.combo.box,Date.combo.box,"Column Sample Size"] #Find sample
curr.val = round(tab[Brand.combo.box,Date.combo.box],0) #Find current quarter value
curr.index = match(Date.combo.box,colnames(tab)) #Find index of current quarter
prev.val = round(tab[Brand.combo.box,(curr.index-1)],0) #Find previous quarter value
diff = curr.val-prev.val #find difference
change = ifelse(diff>0,paste0(" rose by ",abs(diff)," percent to ",curr.val,"%."),
ifelse(diff<0,paste0(" decreased by ",abs(diff)," percent to ",curr.val,"%."),"stayed the same")) #Update text change
#Conditional text based on quarter selected
if(Date.combo.box=="Qtr 1") {
paste0("\U2022 The benchmark results for ", Date.combo.box," see the number of Cola drinkers who prefer ",Brand.combo.box," to be ",curr.val,"%.",
"\n\U2022 The sample size for ",Date.combo.box," is ",base,".")
} else {
paste0("\U2022 In ", Date.combo.box," the number of Cola drinkers who preferred ",Brand.combo.box,change,
"\n\U2022 The sample size for ",Date.combo.box," is ",base,".")
}
- The first line finds the table percentages.
- Lines 2 to 3 return the Column Sample Size and Column % for the selected date and brand in the specified table. The latter is rounded to 0 decimal places.
- Lines 4 to 5 work out the table index or position of the column for the selected date and then return the previous value.
- Lines 6 to 8 calculate the difference between the current and previous value and return the correct text based on whether the difference is positive, negative, or the same. Here, we use the paste0 function and a combination of strings and referenced objects to form the resulting text string.
- The final lines of code form a condition that tells it what the final text string should look like based on whether the first quarter or other quarters are selected. As we wish to use bullet points we include the unicode character of U2022 preceded by a backwards slash. The "\n" character is used to force a new line.
4. OPTIONAL: Go to Properties > LAYOUT and click Wrap text output.
5. OPTIONAL: Go to Properties > APPEARANCE and set Font and Background styling.
6. OPTIONAL: Resize and re-position the output on your page.
Next
How to Change Font Color Based on Data Using R
How to Use Paste Functions to Create Dynamic Text Using R
How to Use the Dynamic Text Box in Displayr
How to Connect Filters to Controls Using R Code
How to Extract Data from a Single Column Summary Table
How to Extract Data from a Multiple Column Table
How to Extract Data from a Multiple Column Table with Multiple Statistics
How to Extract Data from a Multiple Column Table with Nested Data