UPCOMING WEBINAR: DIY Data Cleaning & Prep: Super Fast, Super Good Register now >>

Articles in this section

See more

How to Use Calculation Grid in Displayr

  • Updated
Follow

Calculation Grid brings Excel-like functionality into Displayr. It is designed to work like standard built-in drag-and-drop tables, which allow you to auto-size with its contents, apply table styles, have individual cells referenced in R, etc. However, unlike built-in tables, individual cells can be edited directly and you can even use copy and paste to populate them. Things you would normally do with table rules, like adding and removing rows and columns, can be done with a click.

Typically calculation grids are used to:

  • Combine different types of information from multiple tables. This should not be used in place of combining variables into sets or using Combine Tables to combine full tables together.
  • Do ad hoc calculations on the fly.
  • Append outside data/calculations to tables in Displayr.
  • Calculation grids work best when they contain 30 or less cells. If you need to create a calculation grid that exceeds 30 cells, it may be more efficient to create a custom R Calculation on the page instead. Contact Support if you are unsure of the best solution.

We walk through an example of when to use a Calculation Grid on our blog. An example of a Calculation Grid is below:

Intro.png

This article covers the following features of Calculation Grids:

Requirements

A Displayr license.

Calculation Grid User Interface

To create a calculation grid on a page: 

  1. In the toolbar, select Calculation Calculation.png > Calculation Grid.
  2. The mouse pointer will change to a cross, mceclip0.pngand from that point, you can create your grid.
  3. If you click on the page and immediately release, an empty 2 x 2 grid (including Row and Column headings) is created.

For a larger grid, click on the page and drag the pointer down and to the right until you get the desired number of rows and columns. You will get visual feedback showing the number of rows or columns as you drag the mouse and a counter near your cursor.

CreateCalcGrid.gif

Each cell, including the row, heading, column heading, and the "statistic" cell (top-left) are all individual R calculations.

Each cell has its own name, created by default. You can find the name by going to General > General > Name in the object inspector.

CalcGridCellName_New.png

Manipulating Rows and Columns

Adding rows/columns

You can add columns and rows with the Plus (+) inserter:

AddRowsColumnsCalcGrid.gif

Additional Rows and/or Columns can also be added by:

  1. Select the calculation grid.
  2. From the object inspector, go to General > Rows/Columns.
  3. Type in the desired number of rows or columns.

In this example, we created a Calculation Grid with 7 Rows and 5 Columns.
CalcGrid_OJ_Rows_Columns.png

Deleting rows/columns

To delete a row or column from the Calculation Grid:

  1. Right-click the header of the row or column you want to delete.
  2. From the menu, select Delete Row (or Column).

Delete Row.png

Reordering rows/columns

You can move rows and columns from a menu by:

  1. Right-clicking the header of the row or column you want to move.
  2. Select Move Up or Move Down for rows or Move Left or Move Right for columns.
    Move Left.png

You can also drag to reorder rows and columns.

  1. Click the header of the row or column you want to move.
  2. Drag the NewBrick.png box up or down to where you want to move the row. For columns, drag the box to the left or right.   
  3. Release the mouse button at the desired location.

    CalcGridReorder.gif

Modifying the Calculation Grid Appearance

Aside from stat testing (which the calculation grid doesn't support), you can customize the appearance of the table like you would a normal drag-and-drop table via the Appearance tab in the object inspector, see How to Customize the Formatting of Tables in Displayr. The options will depend on whether you have the entire calculation grid selected or only a cell, but they include:

  • Font
  • Table Style
  • Decimals
  • Fill
  • Number format (either % or $)

Editing Cells in Calculation Grid

Each cell in the calculation grid contains its own formulas and values just like in Excel. However, instead of Excel functions, you will use basic R code to populate the table (don't worry - it's easy!). You can edit cell values by double-clicking in a cell, pressing F2 on your keyboard, or using the R code editor via Data > R Code in the object inspector. 

For example, if I click "Row Label 1" from this grid, the R Code editor shows the code, which, in this case, is "Row Label 1". Because Row Label 1 is a text value, it is entered between double quotes.
CalcGridCellName.png

Numeric values don't require quotes:
CalcGridNumericCell.png

Editing cells using the R Code editor:

  1. Click the cell you want to edit.
  2. Edit it in the R Code editor in the object inspector.
  3. Click outside of the calculation grid for the code change to take effect.

Double-clicking a cell to edit:

  1. Double-click the cell in the calculation grid.
  2. You can directly enter your code into the cell. You can do this for row and column headers, including the top left cell.
  3. Hit Enter when you're done editing.
    CalcGridDoubleClick.png

If you have a lot of code to write, continue typing your code, and the cell with automatically expand. If you need additional lines, hold down the ALT key and click Enter, and keep typing until you get the desired number of lines.
MultipleLinesCalcGrid.gif

Using the F2 key to edit a cell:

Instead of double-clicking, you can use the F2 key to switch to the code editor.

  1. Click the cell in the calculation grid that you want to edit. 
  2. Click F2 on your keyboard.
  3. Type your new code directly in the cell.
  4. Click ESC to exit the cell.

Be sure you enter syntactically correct R Code. For example, to enter text, you must enter it in "double quotes".

Perform Calculations on the Cells

Excel-like calculations, where the inputs are cells in a table, are done by point-and-click. In this example, the detractor percentage is subtracted from the promoter percentage.

  1. Add a new row to the calculation grid.
  2. Double-click into the row header cells.
  3. Give the row a label, such as "Promoters - Detractors". Don't forget to enclose in double quotes for text.
  4. Hit Enter.
  5. Double-click into the cell to start your subtraction formula.
  6. Click the cell you want to subtract from. In this case, it's the "52%" in the "Promoters" row.
  7. Type a minus (-) sign after the selected cell.
  8. Click the cell you want to subtract. In this case, it's the "17%" in the "Detractors" row.
  9. Hit Enter.

    SubtractInCalcGrid.gif

Read more about mathematical functions using R in How to Perform Mathematical Calculations Using R. See the Use Formulas With Code Completion section below for how to easily insert some of Displayr's mathematical functions.

Use Formulas With Code Completion

You can use formulas or R functions within cells in a calculation grid. Displayr supports code completion where function suggestions appear when you start typing. See below for an example.

Some tips:

  • Displayr cares about capitalization. That is, sum, Sum, and SUM are all different things (and most of the time you will be wanting Sum).
  • All calculations that are in the Calculation Calculation.png menu have an R function equivalent. The names in the menus match the function names (e.g., SumEachRow() is the function in R to use that is same as in the menu under Calculation > Sum > Sum Each Row). If you see a mathematical operation in the Calculation Calculation.png menu that you want to use, start typing and the code suggestion will appear.

SubtractInCalcGrid_SumFunction.gif

Programming in R

As mentioned earlier, you can think of cells of a calculation grid in the same way you think of cells in an Excel table. Like Excel, each cell contains its own formulas and values (created via R code). For those who are more familiar with R programming in Displayr, it is worth noting a cell in a calculation grid isn't the same as a standalone custom Calculation though. More advanced functionality may not be available (e.g., you can't use CreateCustomTable() in a cell in a grid). 

If you'd like to explore Displayr's in-house R programming resources see:

Copy/Paste in a Calculation Grid

Calculation Grids support copying and pasting data from tables within Displayr (except from Autofit tables) and even between Excel (only Copy/Paste Values is supported from Excel). You can copy and paste individual cells, or you can copy and paste many cells at once. If you are pasting multiple cells, right-click on the "first" cell you want to paste into, even if it's a row or column header, and select the appropriate paste option.

  1. Right-click the cell(s) you want to copy and select Copy.
  2. Click the target cell(s) and select one of the Paste options:

Paste code

Paste Code pastes the R Code from the item/cell copied into the target cell's R Code window. So if you want to keep the target cell value up-to-date based on a formula that you can modify in the future, you should use this option. The target cell will calculate the code when you click outside of the calculation grid, and the result is shown in the target cell. This option will only be available if the item you have copied contains R Code.

Paste.png

Paste link

Paste Link links the target cell to the cell that was copied, by referencing the Cell Location (Link) in the R code. So if you want to keep the target cell value up-to-date based on the other cell, you should use this option. The Value in the target cell will update as the original output changes. This is most useful when pasting links on multiple cells at a time. If you want to link to a single cell, it's quickest to just use our point-and-click functionality and click on the other cell you want to link. For example:

PasteLinkCalcGrid.gif

Paste values

Paste Values pastes the resulting Value in the target cell. This pastes a Value that will not update as other data changes, as the value is hard-coded in the R Code. For example:
values.png

Paste format

Paste Format pastes the cell format in the target cell. Note that this only applies to font styling, cell shading, and border, and does not include modifying decimal places or adding currency or percentage symbols. For example:

PasteFormat.gif

Navigating Calculation Grid

The are a number of useful Keyboard Shortcuts available to help you move around in the Calculation Grid. They are as follows:

In In-cell editor (not the Object Inspector editor)
Shortcut Action
Enter Commits the code and moves down
Shift + Enter Commits the new code and moves up
Alt + Enter Moves the cursor to a new line
ESC Cancels editing of cell's code
 
Cell selection mode (navigating through cells but not editing them)
Shortcut Action
Tab Moves to the cell on the right
Shift + Tab Moves to the cell on the left
Backspace OR Delete Deletes the formula/text inputted into the cell

Next

How to Export to Excel

How to Use R in Displayr

How to Perform Mathematical Calculations Using R

How to Use Point and Click Inside R Code

Bespoke Analyses

Calculate Anything

Related articles