How to Create an Auto-Updating Custom Analysis Tool

Introduction

This article describes how to create an auto-updating custom analysis tool (also known as a live Standard R widget) in Displayr. This type of tool typically has the following characteristics:

It is connected to data in a way that means when the data updates, the tool itself updates. For example:
- It is connected to a data source in in the Displayr Cloud Drive.
- It extracts data from some other software via an API.
- It reads data from some updatable data source on the internet.
It does not read data from elsewhere within a document (e.g., it does not read data from a table nor a data set).
Can be shared, copied and pasted between Displayr documents.

Requirements

A professional Displayr license.
An analysis output with regularly updating data that you want to share with your company.

Method - How to create your own auto-updating analysis tool

Step 1: Plan

The first step is to work out what you want your analysis tool to do. A well designed auto-updating tool is self-contained, fulfilling all that somebody who needs access to the data would require. To this end, this type of tool is generally able to be manipulated by its end-users in some of the following ways:

By time. For example, changing the start date, end date, and time aggregation.
Filtering by relevant sub-groups. For example, a tool for sales data would be filterable by sales territory.
Into each of:
- Raw data
- A summary table
- A visualization
By being linked to (i.e., a user should be able to use the tool as an input into other analyses).
By being exportable to Excel or PowerPoint as a table or editable chart.

Note, this tool can only be manipulated by users in Edit mode as a published document will only see the final output and not the interface.

If you have questions about this approach, please contact Support at support@displayr.com.

The questions we then need to ask ourselves are:

How do we want to filter our data?
What data formats do we want to show?
What options do we want to include?
Do we want to display the whole date range?

Example:

Let's look at an example where we want to to take the below raw data ...

... and turn it into a table crossed by month...

... and a small multiples chart over time:

In this case, we want to be able to switch between:

Displaying the data at a total level and filtered by gender.
Displaying the data as a raw data table, summary table, and chart.

The below will take us through the various steps to consider when creating an auto-updating analysis tool using this example as a guide.

Step 2: Set up your data export

There are different approaches to how your input data should look. We will begin by first considering the following points:

What data format do we want to use (i.e. raw data or pre-filtered tables)?
How often do we want this export to update?

In this example, we will use the Displayr Cloud Drive for sharing our data, which should be exported periodically from the source document using a Calculation (via Calculation > Custom Code).

1. Raw data

The most flexible approach is to use raw data as the data source for your auto-updating tool. This is the approach we will use in this example.

You can set this up as a table by selecting the relevant variables you want to include in the Data Sets tree and going to Anything > Table > Raw Data > Variable(s) in the toolbar. See How to Create a Raw Data Table From Variable(s).

Importantly, the variables that you want to filter your data by must be included in this data set. Note, if you match the names of the columns to your control filter options, you can simplify your R code.

2. Pre-filtered tables

When you only wish to show aggregated tables and don't have many filtered views, you can instead set up, for example, a calculation with a list which includes all versions of your tables. Below are the 3 views from our example set up as separate tables:

tab = list(Total = table.Date.by.Preferred.cola,
      Males = table.Date.by.Preferred.cola.2,
      Females = table.Date.by.Preferred.cola.3)

3. Export your data

Finally, we save our data as an .rds data set to the cloud drive at regular intervals using our flipTime and flipAPI packages. In the below calculation, we will export raw.data daily as cola.preference.rds:

flipTime::UpdateAt("01-01-2021 09:00", units = "days", frequency = 1, options = "wakeup")
flipAPI::QSaveData(raw.data,'cola.preference.rds')

See How to Automatically Update Calculations, Variables and Data Sets Using R and How to Access the Displayr Cloud Drive Using R for details.

Step 3: Decide on what input controls to include

As one of the main purposes of a live analysis tool is to allow the user to switch between displaying different filtered or formatted views, input controls are an essential feature.

Options include:

Numeric and text fields
Combo and check boxes
Drop box controls
Color pickers

We now must consider the following in this scenario:

What control types are best for our options?
How often do we want this tool to update, and should the user be able to adjust this?

Step 4: Add input controls

In our example, we will create a series of user inputs:

A combo box control for switching between views (i.e. Total, Males and Females).
A combo box for switching between formats (i.e. Raw data, Table and Chart).
A combo box, numeric box, text box, and checkbox for updating frequency and timing settings.

1. Select your calculation and go to Properties > INPUTS JAVASCRIPT. This is where user controls are added. They are written using JavaScript.

2. We begin by adding a heading to the input form.

3. You then have the option of adding a group. In this example, however, we only have 2 controls outside of the Updating group, so we will ignore this.

3. Next, add the first user control and take note of its arguments.

4. Set the name to make it easier to reference in the subsequent steps.

5. Set the label to be useful for the user.

6. Set the alternatives for the combo boxes.

7. Set the default_value so the most common use case is catered to without the user having to do anything.

8. Add a prompt to aid the user so that when they hover over the control it provides a fuller explanation where necessary.

9. Repeat the above for all required controls.

10. Include the below Updating group template code for setting automatic updating (taken from How to Automatically Update a Page). Note, if you wish to force your document to publish when your tool updates, you should tick the Update exported documents option.

Here is the code for all user inputs in our tool:

form.setHeading("Brand Preferences - Live Widget") 
// Add filter control
form.comboBox({name: "formFilter",
label: "Filter by",
prompt: "Select the categories to crossbreak your data",
default_value: "Total",
alternatives: ["Total","Males","Females"]})
// Add format control
form.comboBox({name: "formOutput",
label: "Show as",
prompt: "Select the output type",
default_value: "Chart",
alternatives: ["Raw data","Table","Chart"]})
// Add group for automatic updating
form.group("UPDATING")
var period = form.comboBox({name: "formUpdatePeriod", label: "Update period", 
alternatives: ["Months", "Weeks", "Days", "Hours", "Minutes", "Seconds"], default_value: "Days", prompt: "The time units for updating"}).getValue();
var defaultFrequency = 1;
if (period == "Seconds")
defaultFrequency = 600;
else if (period == "Minutes")
defaultFrequency = 10;
form.numericUpDown({name: "formFrequency", label: "Frequency", default_value: defaultFrequency,
prompt: "The update frequency in units of the update period", increment: 1, minimum: defaultFrequency, maximum: 1000000});
var start = form.textBox({name: "formStart", label: "Start date and time", prompt: "The first update date and time",
required: false, prompt: "Default now, or e.g. 31-12-2018 18:00:00"}).getValue();
if (start != "") {
form.checkBox({label:"US date format", name:"formUSDate", default_value:false, prompt: "Specify update start date as mm-dd-yyyy"});
form.textBox({name: "formTimeZone", label: "Time zone", 
required: false, prompt: "Leave blank for UTC or enter e.g. America/New_York"}); 
}
form.checkBox({name: "formSnapshot", label: "Update exported documents", default_value: false, prompt: "Whether exported documents should be updated"});

See How to Add Headings, Groups and Pages to a Custom Analysis Tool, How to Create User Input Fields in a Custom Analysis Tool, How to Set Conditional Controls in a Custom Analysis Tool and How to Set Object Types in DropBox Controls in Custom Analysis Tools.

Step 5: Add your main R code script

We now write our R code by referencing the relevant user input control names in the previous step.

1. Go back to Properties > R CODE.

2. Add the below template code to set the automatic updating using our flipTime R package:

library(flipTime)
library(flipAPI)
# Refresh data
options <- ifelse(formSnapshot, "snapshot", "wakeup")
if (formStart != "") 
{
if (formTimeZone == "") formTimeZone <- "UTC"
UpdateAt(formStart, us.format = formUSDate, time.zone = formTimeZone,
units = tolower(formUpdatePeriod), frequency = formFrequency, options = options)
} else
UpdateEvery(formFrequency, units = tolower(formUpdatePeriod), options = options)

3. Import your data set. In this example, we are using the Displayr Cloud Drive to store the updated data so we call our .rds data set using the QLoadData function:

# Get the data
q = QLoadData("cola.preference.rds")

4. Format your raw data to fit the requirements of your tabular output:

# Set month
q$Month = Period(AsDate(q$Date), "month")

# Filtering
f = rep(TRUE, NROW(q))
if(formFilter == "Males")
    q = q[q$Males == 1,]
if(formFilter == "Females")
    q = q[q$Females == 1,]
out = q[f,]
# Frequency table
t = table(out[,c(5,2)])

We begin by using the Period function to aggregate the date as a month for the chart view and add it to our data.
We then filter our data by first declaring f as TRUE for all records and then apply our formFilter gender filters to redefine f as FALSE when not included in the selected filter.
The filtered data is then declared as out using the updated boolean definition for f.
Finally, we select the aggregated month (column 5) and preferred cola (column 2) columns and create a table called t using the table function.

Note, if we used pre-filtered tables instead of raw data with matching filter names, we could simplify the code in the last 2 steps to the following:

# Get the data
q = QLoadData("cola.preference.rds")
out = q[[formFilter]]

5. Choose what output should be displayed:

if(formOutput == "Table") {
  out = t
} else if(formOutput == "Chart") {
  require(flipChart)
  out = CChart(chart.type = "Line",
        x = t,
        color = c("#3e7dcc","#04b5ac"),
        opacity = 0.7, 
        x.tick.angle = 45,
        fit.type = "Friedman's super smoother",
        panel.title.font.size = 13,
        small.multiples = TRUE, 
        pad.top = 0.2,
        grid.show = FALSE,
        margin.top = 0,
        margin.bottom = 0,
        margin.left = 0,
        margin.right = 0,
        fit.line.width = 2,
        fit.line.type = "dot")
}

cola.preferences = out

We wrap this part of the code in an if...else condition based on the formOutput control.
If a chart is the selected format, we need to then specify the various arguments using flipChart's CChart function to tell it how to display, i.e. chart type, colors, styling, and margins.

See How to Reference Controls in a Custom Analysis Tool Using R and How to Create Chart Templates Using R Functions.

Step 6: Check your code

1. Go through each combination of controls (where appropriate) looking out for any red error messages and orange warnings that should not appear.

2. If you do encounter errors, work out whether you need to further restrict inputs or whether the issue is the code itself, e.g. a misspelled reference.

Here is the updated R code in full:

library(flipTime)
library(flipAPI)
# Refresh data
options <- ifelse(formSnapshot, "snapshot", "wakeup")
if (formStart != "") 
{
if (formTimeZone == "") formTimeZone <- "UTC"
UpdateAt(formStart, us.format = formUSDate, time.zone = formTimeZone,
units = tolower(formUpdatePeriod), frequency = formFrequency, options = options)
} else
UpdateEvery(formFrequency, units = tolower(formUpdatePeriod), options = options)

# Get the data
q = QLoadData("cola.preference.rds")
# Set month
q$Month = Period(AsDate(q$Date), "month")

# Filtering
f = rep(TRUE, NROW(q))
if(formFilter == "Males")
    q = q[q$Males == 1,]
if(formFilter == "Females")
    q = q[q$Females == 1,]
out = q[f,]
# Frequency table
t = table(out[,c(5,2)])

if(formOutput == "Table") {
  out = t
} else if(formOutput == "Chart") {
  require(flipChart)
  out = CChart(chart.type = "Line",
        x = t,
        color = c("#3e7dcc","#04b5ac"),
        opacity = 0.7, 
        x.tick.angle = 45,
        fit.type = "Friedman's super smoother",
        panel.title.font.size = 13,
        small.multiples = TRUE, 
        pad.top = 0.2,
        grid.show = FALSE,
        margin.top = 0,
        margin.bottom = 0,
        margin.left = 0,
        margin.right = 0,
        fit.line.width = 2,
        fit.line.type = "dot")
}

cola.preferences = out

Step 7: Add your analysis tool to Displayr's toolbar menu (for Enterprise users)

Enterprise users have the added option of being able to add custom tools to the Anything menu under your company name. These are stored as RScripts in your Displayr Cloud Drive.

See How to Share a Custom Analysis Tool Between Documents.

Resources: Learning R

How to Learn R

Using R in Displayr Video Series

How to Use R in Displayr

How to Work with Data in R

How R Works Differently in Displayr Compared to Other Programs

How to Reference and Distinguish between Different R Objects in Displayr

How to Use Point and Click Inside R Code

How to Use Different Types of Data in R

How to Work with R Data Sets

How to Work with R Loops