This article describes how to go from a crosstab table in Q...
...to an Editable PowerPoint Snake Chart:Note: you can create a Snake chart in Q (Show data as Table > Line and Dots Charts > Snake Plot) and export it to PowerPoint as an image. PowerPoint doesn't support a Snake Chart type output. Nevertheless, this type of output is often used in reporting. Researchers have found a workaround involving restructuring the data and charting it as a Scatter plot. This article contains the steps to automate the process.
Method
- Create a crosstab selecting the question you wish to show as lines in Columns (age in the table above).
- Go to Create > R Output.
- Update and input the below code into Properties > R Code.
You must update the table name in row 2 of the code. You can find the table's name by right-clicking on the table in the Report tree and selecting Reference Name...
#specify table
thetab=table.Income.by.Age
#load reshape
library(reshape2)
#remove nets
thetab=thetab[rownames(thetab) != "NET",colnames(thetab) != "NET" ]
#melt the table to long format
thetab.m=melt(thetab)
#create a similar table for the axes
axis.m=thetab.m
#change the values to the sequential axis values based on how many columns were melted
axis.m[,"value"]=seq(NROW(axis.m)/length(unique(axis.m$Var2)),1,-1)
#merge the two long data together
merged=Cbind(thetab.m,axis.m)
#make the data wide keeping pcts long but axis values into individual columns
wide = dcast(merged, thetab.m.Var2 + thetab.m.Var1 + thetab.m.value ~ axis.m.Var2, value.var="axis.m.value")
#remove the unneeded column names
wide.2 = wide[,!colnames(wide) %in% c("thetab.m.Var2")]
wide.2[,-1] - The above code will reformat the table into the correct format for the desired chart type.
- Select the output, and click on the PowerPoint icon in the toolbar.
- Set Format to To PowerPoint as Excel Chart.
- Set Chart to XY Scatter Lines (or another Scatter type).
- Be sure that the Use legacy exporter (slower) box is unchecked.
- Select OK.
- Select Create New Presentation > OK.
- Open the exported file in PowerPoint and re-format the chart.
Next