BEST Viewpoints Spreadsheets

BEST Viewpoints spreadsheet like interfaces provide users the capability of performing operations like data transformation, calculation of new fields and summaries, selecting/deselecting fields, sorting data by one or more fields or columns, and querying datasets. After defining any combination of these operations the user can save a Template where all transformations, calculations, queries, etc are saved for future use in similar datasets. All operations are always executed in the order shown (i.e. Transform, Calculate, Select, Move, Sort, and Query), and should also be defined in this way.

There are two spreadsheets available and are described in the next two sections. In general these two spreadsheets are similar but the Advanced Spreadsheet can operate on the output of the Basic Spreadsheet and can also group calculations by levels of one or more categorical variables.

To transform fields just select the field and the desired composition of transformations. The transformation can also be edited in case the desired transformation is not provided in the menu.

In the tab Calculate new fields (Add Fields) or summaries (Add Summaries) can be calculated.

A tooltip for adding fields pops up to help understanding how to add new fields. New fields are always appended at the end of the dataset. The tab Move can be used to change the position of new or previously existing fields.

As an example let's add the new field KSales as shown below. Once the equation and field label are defined click on Load to calculate the new field.

Note that the KSales is automatically appended to the dataset and that the equation defined for KSales remains displayed in the window New Fields.

The tab Select is used to select those fields which should be included in the output dataset. In the example below MonthNo was deselected.

To sort data select field names in the order in which sorting will occur. In the example below data will be sorted by Product (descending) and then by Employee (ascending).

The advanced Spreadsheet performs all operations performed in the basic but can also use as input the output of the Basic Spreadsheet. Note that changing data sources causes the reset of all definitions made in the current spreadsheet (calculations, queries, etc.). For illustration purposes the output of the basic spreadsheet just calculated will be used as input. Note that after selecting the dataset, the data shown is exactly the output of the basic spreadsheet and not the originally loaded dataset.

Another difference is that in this spreadsheet calculations can be grouped by the levels of one or more categorical fields. Indeed in the Group tab fields are ordered such that those with fewer distinct elements are shown first. Each field button has associated tooltips describing the composition of the field. Not all fields will be displayed here by default. For more details about this read the section BEST Viewpoints Options.

In the example below data has been grouped by Country. Note that at this point there are no calculations or queries defined.

Summaries are described in a tooltip associated to the Add Summaries tab as described below.

Three summaries were added as shown below. Note that now the output has two tabs: Data and Summaries. To view the summaries just click on that tab. Note that TotalSales for France is 86,629.

The Summaries button is used to display the summaries in tabular form as shown below.

In the image below only the summaries are being displayed and these have been sorted descending by TotalSales.

Note that queries can be defined on both, the output data or the summaries. In the example below a query on the summaries is being defined on TotalSales.
When more than one statement is defined as a query the wrapping logical operator can be selected (And, Or, Nand, Nor, Xor).

Once all calculations are made the output can be delivered by means of several options in the File menu. Send to Notebook will send the output to a new notebook such that it can be saved as a self contained document which can be referenced latter. Save allows the user saving the results in a file. The output can be All (Summaries and Data), Data Only, Summaries Only, or Rectangular Dataset which saves the data as a single file even when the data has been grouped.

View + Script pastes Spreadsheet Commands on a new notebook which will help advanced Mathematica users to recreate the calculations made on both spreadsheets by means of a Mathematica script.

To avoid loosing all your work after finishing defining all equations and queries on both spreadsheets a Template (similar to a macro) can be defined to store all current definitions in both spreadsheets. These templates can be uploaded latter on datasets containing the same field labels as those contained in the dataset where the templates were created. The effect is that all the work done (transformations, equations, sort, queries, etc.) in both spreadsheets will be repeated when the template is loaded.

The user might want to load a Template on a dataset that does not contain all fields contained in the dataset where the template was created. In this case the new dataset must contain all key labels used on the creation of the template.