Stat-Xplore Documentation

Vertical Ellipsis

This section will cover:

  • Introduction
  • Total
  • Codes
  • Add Derivation...(Advanced)
    • Accessing a Derivation
    • Using a Derivation
    • Formulating a Derivation
    • Deleting a Derivation
    • Derivation Functions
    • Further Derivation Functions
  • Knowledge Check


Introduction

The learning objectives for this page are:

  • How to better analyse the data in your table

Within Table View of Stat-Xplore, the Vertical Ellipsis allows a more functions to fully customise you bespoke table. These functions incudes totals, codes, and Advanced. Please scroll to the section relevant to you to learn more about how to use them.



Totals

Even though Stat-Xplore automatically adds totals to tables, you can choose whether to show totals.

In your currently open table to show or hide totals,

  1. Click the ‘Download format list ’ menu icon next to the field name and click the ’Total’ button in the drop-down menu.

    2. Download format list

  2. Depending on if your table had totals or not, Stat-Xplore will then change the table to the opposite to either include (with tick) or no (no tick) totals.

    3. Download format list

    In cases where it may not make sense to include a total (e.g. a time series), a setting has been configured that prevents you from adding a total for that particular field.



Codes

By default, the tables show the names of the values (such as ‘Male’ and ‘Female’). Codes can be particularly useful for the unique geographical codes as these can be used to conjunction with other external software for mapping and GIS.

If you would like to see the ‘Codes’ option in your table instead,

  1. Click the ‘Download format list ’ menu icon next to the field name and click the ’Codes’ button in the drop-down menu.

    2. Download format list

  2. Depending on if your table started with codes or not, Stat-Xplore will then change the table to the opposite to either include (with tick) or no (no tick) totals.

    3. Download format list



Derivations (Advanced)

Derivations allow you to create new calculated items within a table. This is similar to the formula bar that you would use in Excel. For example, using derivations in Stat-Xplore allows you to add together values in other columns or use mathematical and statistical functions.

To visualise this, in the screenshot below a derivation has been added to calculate the total number of customers who are in the North (the total number of Universal Credit claimants in Scotland, the Northeast and the Northwest).

Download format list

Some characteristics of derivations are that:

  • If you save your bespoke table in Stat-Xplore, it will remain as long as you do not remove the field they are derived from or change the set of field values from the derived field that are in the table
    • e.g. The above derivation for ‘Region’ would automatically be removed if you removed the field value ’Northeast’ from the table or added a field value that didn't already exist in the table
  • Changes to other fields do not affect derivations
    • e.g. Adding or remove field values from ‘Gender’ would not affect the derivation on ‘Region'
  • You can move the derived field to a different axis without losing your derivations

Accessing a Derivation

To use a derivation in your currently open table.

  1. Click the ‘Download format list’ option in the table next to the name of the field and select the ‘Add Derivation’ button in the drop-down menu.

    2. Download format list

  2. Stat-Xplore will then open the ‘Define Derivation’ dialog display where you can create your derivation. Within the ‘Name’ field, you can enter a name for the derivation. This will then be displayed in the table. Use the ‘Formula’ field to enter your formula.

    3. Download format list


Formulating a Derivation

When you are in the ‘Define Derivation’ dialog display, you can either type in your formula manually or use the in-built system.

To use the Stat-Xplore in-built system,

  1. You can use the buttons located at the bottom of the dialog display and the Field Values list on the left-hand side to select and add field items and mathematical and statistical function.
  2. To refer to a field value in your formula, you must use the value codes (in this example, the code V1 represents Northeast, V2 represents Northwest, and so on).
  3. In the Field Values list, double click to select the field items you want to use with the function from the drop-down list. if the number of selected items does not match the number of arguments they take, some of the functions may be greyed out.
  4. To make multiple selections, hold down the ‘Shift’ or ‘Ctrl’ Keys.
  5. Click the ‘Add’ button.
  6. Once you have finished creating your formula, click the ‘Create’ button to add it to your table.
  7. Stat-Xplore will then compute your derivation. The screenshot below shows an example of a completed derivation:

    8. Download format list


Editing a Derivation

If you want to make changes to a derivation after you have created it,

  1. Click the menu next to the item in the table and select ‘Edit Derivation’.
  2. Stat-Xplore will then display the Define Derivation dialog, where you can then make changes.
  3. When you have finished, click Save to save your updates.

Deleting a Derivation

To remove a derivation,

  1. Click the menu next to the item in the table
  2. Select ‘Delete Derivation’.


Derivation Functions

In addition to the mathematical operators (such as +, -, * and /) you can also use some functions in your derivations:

Exp Exponential function. For example: Exp(V1)
Sqrt Square root. For example: Sqrt(V1)
Round Rounds to 0 decimal places. For example: Round(V1)
LnLogarithm function. For example: Ln(V1)
Sum

The sum of a range of field values.

For example: SUM(V1:V4) calculates the sum of field values V1, V2, V3 and V4.

Forecast

A forecast of a future or past value. The forecast is based on a trendcast algorithm that uses linear regression to extrapolate forwards and backwards.

The function takes 2 arguments, separated by a semi-colon:

  • The range of values to use in the forecast. For example: V1:V5
  • The period to forecast for. You can use a negative value to forecast for an earlier period, and you can use a decimal value to forecast for a partial period.

For example:

Forecast(V1:V5;1)
Forecast the next item in the sequence after V1 to V5. If V1 to V5 represent the years 2011-2015, then this forecasts the result for 2016.
Forecast(V1:V5;2)
Forecast the next but one item in the sequence after V1 to V5. If V1 to V5 represent the years 2011-2015, then this forecasts the result for 2017.
Forecast(V1:V5;-1)
Forecast the previous period before the start of the range V1 to V5. If V1 to V5 represent the years 2011-2015, then this forecasts the result for 2010.


Further Derivation Functions

Within the ‘Define Derivation’ dialog display, located at the bottom left is the ‘Advanced’ button to access some advanced options. The advanced options are as follows:

Position

The position of the derivation within the table. By default, derivations are added as the last item in the row or column.

Select an option from the drop-down list to move the derivation to either the start, end or after a specific field value.

Calculation Order

Set the order of evaluation for the derivation.

This can be important if you have multiple derivations in a table (for example, if you have derivations in both rows and columns, what should Stat-Xplore display in the cell where the two derivations intersect?). It can also be important if you are calculating percentages of fields and using totals.

Fields with a lower number are evaluated first.

Either enter a number or click Last to set the derivation to be evaluated last out of all currently defined derivations (when you click Last, Stat-Xplore populates the Calculation Order field with an evaluation number that is higher than any other currently defined derivation).

Decimals Enter the number of decimal places to display for this derivation.


Further Questions

If you still have any questions that are not answered in the guide, please feel free to email Stat.Xplore@dwp.gov.uk



Check your Knowledge!

What can codes be beneficial for? (Hint: There are two answers)

A. GIS

B. Easy comprehension

C. Downloading tables

D. Other external softwares


Show/hide Solution Test2 Show Answer

Attachments:

SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-With-Filter.png (image/png)
SW2-Filters-Add-1.png (image/png)
SW2-Filters-Add-2.png (image/png)
SW2-Filters-Remove.png (image/png)
SW2-Filters-Remove.png (image/png)
SW2-Filters-Add-1.png (image/png)
SW2-Filters-Add-2.png (image/png)
SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-With-Filter.png (image/png)
SW2-Default-Summation-Filter.png (image/png)
SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-With-Filter.png (image/png)
SW2-Default-Summation-Filter.png (image/png)
SW2-Filters-Add-1.png (image/png)
SW2-Filters-Add-2.png (image/png)
SW2-Filters-Remove.png (image/png)
SW2-Filters-Add-1.png (image/png)
SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-With-Filter.png (image/png)
SW2-Default-Summation-Filter.png (image/png)
SW2-Filters-Add-2.png (image/png)
SW2-Filters-Remove.png (image/png)
SW2-Filters-EG-No-Filter.png (image/png)
SW2-Filters-EG-With-Filter.png (image/png)
SW2-Default-Summation-Filter.png (image/png)
SW2-Filters-Add-2.png (image/png)
SW2-Filters-Remove.png (image/png)