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 StatXplore, 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 StatXplore automatically adds totals to tables, you can choose whether to show totals.
In your currently open table to show or hide totals,
 Click the ‘ ’ menu icon next to the field name and click the ’Total’ button in the dropdown menu.
 Depending on if your table had totals or not, StatXplore will then change the table to the opposite to either include (with tick) or no (no tick) totals.
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,
 Click the ‘ ’ menu icon next to the field name and click the ’Codes’ button in the dropdown menu.
 Depending on if your table started with codes or not, StatXplore will then change the table to the opposite to either include (with tick) or no (no tick) totals.
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 StatXplore 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).
Some characteristics of derivations are that:
 If you save your bespoke table in StatXplore, 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.
 Click the ‘’ option in the table next to the name of the field and select the ‘Add Derivation’ button in the dropdown menu.
 StatXplore 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.
Formulating a Derivation
When you are in the ‘Define Derivation’ dialog display, you can either type in your formula manually or use the inbuilt system.
To use the StatXplore inbuilt system,
 You can use the buttons located at the bottom of the dialog display and the Field Values list on the lefthand side to select and add field items and mathematical and statistical function.
 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).
 In the Field Values list, double click to select the field items you want to use with the function from the dropdown list. if the number of selected items does not match the number of arguments they take, some of the functions may be greyed out.
 To make multiple selections, hold down the ‘Shift’ or ‘Ctrl’ Keys.
 Click the ‘Add’ button.
 Once you have finished creating your formula, click the ‘Create’ button to add it to your table.
 StatXplore will then compute your derivation. The screenshot below shows an example of a completed derivation:
Editing a Derivation
If you want to make changes to a derivation after you have created it,
 Click the menu next to the item in the table and select ‘Edit Derivation’.
 StatXplore will then display the Define Derivation dialog, where you can then make changes.
 When you have finished, click Save to save your updates.
Deleting a Derivation
To remove a derivation,
 Click the menu next to the item in the table
 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)  
Ln  Logarithm function. For example: Ln(V1)  
Sum  The sum of a range of field values. For example:  
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 semicolon:
For example:

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 dropdown 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 StatXplore 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, StatXplore 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
Attachments:
SW2FiltersEGNoFilter.png (image/png)
SW2FiltersEGWithFilter.png (image/png)
SW2FiltersAdd1.png (image/png)
SW2FiltersAdd2.png (image/png)
SW2FiltersRemove.png (image/png)
SW2FiltersRemove.png (image/png)
SW2FiltersAdd1.png (image/png)
SW2FiltersAdd2.png (image/png)
SW2FiltersEGNoFilter.png (image/png)
SW2FiltersEGWithFilter.png (image/png)
SW2DefaultSummationFilter.png (image/png)
SW2FiltersEGNoFilter.png (image/png)
SW2FiltersEGWithFilter.png (image/png)
SW2DefaultSummationFilter.png (image/png)
SW2FiltersAdd1.png (image/png)
SW2FiltersAdd2.png (image/png)
SW2FiltersRemove.png (image/png)
SW2FiltersAdd1.png (image/png)
SW2FiltersEGNoFilter.png (image/png)
SW2FiltersEGWithFilter.png (image/png)
SW2DefaultSummationFilter.png (image/png)
SW2FiltersAdd2.png (image/png)
SW2FiltersRemove.png (image/png)
SW2FiltersEGNoFilter.png (image/png)
SW2FiltersEGWithFilter.png (image/png)
SW2DefaultSummationFilter.png (image/png)
SW2FiltersAdd2.png (image/png)
SW2FiltersRemove.png (image/png)