Creating calculated fields
SolveXia Analytics & Reporting provides a simple way to create powerful reports based on managed tables. With the latest BI and data visualisation techniques, you can create reports that communicate and inform effectively.
This article will guide you through the creation of a calculated field. Users can reference fields from the available data with custom formulas, pre-defined formulas, or SQL statements to create ‘calculated fields’. These calculated fields can then be used in the creation of a report. In this example, we will create a calculated field which involves a simple formula.
Before we get started
Before you start, please ensure that you have a managed table with at least one row of data. If you need help to create a managed table or to import data into a managed table, please refer to the articles below.
- How to create a managed table
- How to import data from an excel file into managed table
- How to import data from text file into managed table
- How to import data from access file into managed table
- How to create a report with a pie chart
1. [highlight txt=’Create’] a new Report with the managed table used in the How to create a tabular report with Drill-Through capability tutorial.
2. At the ‘Report Data’ stage, [highlight txt=’drag’] the ‘AirlineInfo_Date’ and ‘AirlineInfo_Amount’ field from the [highlight txt=’Data Field’] pane into the [highlight txt=’Columns’] pane.
3. [highlight txt=’Aggregate’] the ‘AirlineInfo_Amount’ field.
4. Click the ‘Add Calculated Field’ link at the bottom-left corner of the ‘Data Fields’ pane. A new browser window will pop up (please make sure that the ‘pop-up blocker’ of your browser is not blocking pop-ups from the website).
5. Type “Amount times 1000” into the ‘Calculated Field Name’ text field.
6. The dropdown for ‘Formula Type’ should be selected to ‘Simple Formula’ by default (the other option is Pre-defined Formula. Selecting this option will allow the user to choose from various Functions will can be applied to any selected data fields. These Functions allow the user to create calculated fields which allow the user to, for example, calculate the ratio between the numbers in two different columns, calculate the months between the dates in two different columns, or calculate the days between the numbers in a particular column and the current date).
7. The field selector should be set to ‘- – Select Field – -‘ by default. Click the selector.
8. A dropdown containing the names of all the available fields will appear. Select ‘AirlineInfo_Amount’.
9. Click the [highlight txt=’+Add’] button next to the selector.
10. The words ‘AirlineInfo_Amount’ will appear in the workspace. The selector will also change back to its default value of ‘- – Select Field – -‘ while the ‘+Add’ button deactivates and turns to gray. The mathematical function buttons will activate. Click the ‘multiplication’ button.
11. The ‘multiplication’ sign will appear in the workspace immediately after the words ‘AirlineInfo_Amount’. Type “1000” into the number field to the right of the field selector.
12. Click the [highlight txt=’+Add’] button next to the number field.
13. Click [highlight txt=’Save’].
14. There should now be a red folder in the ‘Data Fields’ pane named “Calculated Fields”. Click on it.
15. The folder will open up, revealing the “Amount times 1000” calculated field you just created. Drag the calculated field to the ‘Columns’ pane and then aggregate it.
16. [highlight txt=’Navigate’] to the ‘Preview’ stage: