Now, let’s start by discussing a simple framework that describes how calculations fit into data analysis workflows.
Goal = Better Decisions
Simply put, we collect data to inform and make better decisions that produce value.
Data Value Chain
And we go through several stages to turn data into value. In market research, we start by capturing data via surveys, but you can see other collection methods for other data types here as well.
We store collected data and then clean, tidy, and restructure it.
Then, we move onto tabulation, which is a broad term for a variety of analysis methods.
In Displayr, tabulation is very straightforward. While it’s not our focus today, this is how you tabulate in Displayr for those of you who are new to Displayr.
This crosstab here would be much more difficult and time-consuming to create in Excel. You’d have to select the data, create or insert a pivot table, select what to include in the pivot table, select what values to count, etc. It’s a pain, and even after all that work, you wouldn’t have the stat testing you see here that Displayr does automatically.
Another broad group of analyses are advanced analyses, like regression.
As you can see, advanced analyses like regression are easy to perform w/ simple drag and drop in Displayr, but like tabulation, they’re not our focus today.
Moving along the Data Value Chain, we then work out which analyses are interesting. This is the Discovery stage.
Then, we often do simple calculations, usually in Excel or Google Sheets, and then we visualize them, create a story, and share it w/ stakeholders.
The simple calculations we do here are what we at Displayr like to call “bespoke analyses” for lack of a better term. These bespoke analyses are our focus today. How do we use Displayr to perform all the simple calculations that we’d normally do in Excel or Google Sheets? We’ll answer that question during this webinar.
We should want to do bespoke analyses in Displayr, since that means they’ll always be hooked up to our visualizations and everything will update automatically when we update our data, like when we filter to focus on a specific segment or get a new wave of data for a tracker.
If instead, we’re always exporting to Excel or Google Sheets and then doing calculations there, everything’s disconnected and we end up doing calculations by hand, which is slow and error-prone. There’s a better way, and it’s doing calculations in Displayr.
Simple Calculations… Calculate Anything
Let’s start w/ a very simple calculation, something as simple as adding 1+1. Simple math works the same way in Displayr as in Excel.
We can do a lot more in Displayr than in Excel however.
For example, if I wanted to create a table that summarizes respondents’ ages, I can write code, using the R programming language, and I could even use ChatGPT to generate the R code for me if I don’t know it off the top of my head.
You can see just like in Excel and Google Sheets, you can select things by simply clicking on them in Displayr. I just start typing the code and then click on what the code should reference or use. There’s no need to type in the variable name.
Now, what about a crosstab? Let’s ask ChatGPT.
Now, it’s not as nice as Displayr’s native crosstabs. It doesn’t have stat testing or percentages, but w/ some more work we could get there.
And of course, there’s no point in writing code to do the things that we can do w/ simple drag and drop. I’m just starting off by showing you the mechanics of calculating anything in Displayr.
Calculations with Whole Tables as Inputs
Here’s a summary table showing respondents’ main phone companies.
And here’s data in Excel that I’ll copy and paste into Displayr.
Now, let’s calculate total annual revenue for each phone company.
This time, I’ll use the whole table as an input for the calculation.
And I’ll multiply the entire table by market size and average revenue per user to calculate total annual revenue.
We could’ve done this in Excel, but Displayr‘s much smarter. For example, if I sort and combine rows, the calculation updates automatically.
Calculations with Parts of Tables as Inputs
Let’s say I want to visualize the “Female” column of this crosstab here.
I can just select the cells in the “Female” column and then create a visualization the normal way that references those cells and uses them as its data source.
Looking at the data source, we see it’s actually a calculation that selects rows 1 through 9 and the second column from the crosstab.
Now, the calculation will always select the second column, even if it changes, but like in Excel, we can create a fixed reference, by pressing the “F4” key.
Now, the calculation explicitly references the “Female” column regardless of its position, so if it moves, the calculation stays up-to-date.
Calculations in Visualizations and Other Places
We can also create more customized calculations when creating visualizations.
I’ll insert a gauge visualization.
The gauge isn’t connected to any data yet. I want to connect it to the Net Promoter Score variable, but first I need to calculate the average Net Promoter Score. I can connect the gauge to the Net Promoter Score variable and calculate its average in one step.
Code Completion Example
What if I want to calculate the average of multiple columns in a table?
Code completion shows a lot of options that start w/ “Average.” I’ll choose “AverageEachColumn” and then select the columns for which I want averages.
Now, I got the averages I wanted, but they’re in a column instead of a row. I’ll just swap or transpose them. Again, you could use ChatGPT to quickly get the code.
Calculation Grid
Often in research, we want to create a bespoke table.
For example, we may want to create a table of words or show some simple math.
We can do things like these in Displayr using a calculation grid.
In Excel, you just type straight in. Calculation grids are a bit different. If we want text, we surround it in quotation marks.
Like in Excel, I can hit the Enter key to quickly enter data and move to the next cell.
OK, so now we see a table like those you can create in PPT w/ both text and numbers.
But you can’t do the following in PPT. We’re going to subtract costs from revenue in the calculation grid to calculate profit.
There you go.
Pasting Tables Into a Calculation Grid
There are a lot of different ways to glue together data from multiple tables. Now w/ a calculation grid, we can do it in Displayr just like how it’s done in Excel.
I just select the row labels from the first table, right click, and copy them.
And then I select the first row label in the calculation grid, right click, and paste the labels and percentages I copied from the first table. You’ll see I can paste values or links. I’ll paste links, so the calculation grid updates if the table updates.
A scrollbar appears because we added extra rows to the calculation grid.
I’ll update the column labels and copy and paste in the share percentages as links.
I can tidy the calculation grid up by deleting things.
Now, let’s add an extra column.
And I can add in a conversion formula to calculate the conversion rate for Burger Shack.
And just like in Excel, I can drag down to apply the conversion formula to the other brands.
And then I can format cells however I want.
