Use the Pivot Analysis in Soft1 Browsers/ Lists, to keep up to date with the progress of significant business data, by performing dynamic groupings and filtering of results, as well as calculations with the use of mathematical functions.
To create a Pivot report work as follows:
① Select the Browser with the data you are interested in, e.g. a Sales Statistics. Add columns with the extra fields you may need for the analysis, for example, Geographical Area, Salesperson etc. The Browser must include at least one numeric field, which displays totals.
② Click on the Pivot Analysis icon, to automatically display your data in Pivot.
- Grouped data/fields are displayed on the left side of the screen.
- All numeric fields, including their totals, are found on the right side.
- All available fields of the Browser/ list, which can be used at Pivot, are displayed at the top.
③ Change the design of the Pivot by arranging its fields the way you want. To move a field from one area to another,
- Right click on a field and select Move to..., or drag & drop between the areas.
- Right-click to Expand/ Collapse any cell/column/columns.
④ Sort the data that require further analysis or processing, by using the options available on the right-click of each field. To perform sorting on grand totals, e.g. the Sales Value, right-click on the field's title. To cancel the sorting, simply deselect it.
⑤ Grand totals, can be further processed/analysed by using built-in mathematical Functions.
- The available functions are: Sum, Count, Min, Max, Average, StdDev, StdDevP, Variance, VarianceP
The function types are the following:
- Absolute: Returns the difference between the columns (previous-next).
- Percent: Returns the percentage of difference between the columns (previous-next).
- Percent Of Column: Returns the percentage of the column in total.
- Percent Of Row: Returns the percentage of the column to the total.
Example: Given your Pivot includes Sales Values of 2 fiscal Years, per Business Unit and Commercial Category. By right-clicking on the Sales Value field and selecting:
- the Absolute function type, you will view in the totals the difference of sales value between the last Year and the Year before.
- the Percent function type, you will view in the totals the percentage of the sales value difference between the last Year and the Year before.
- the Percent of column function type, you will view in the totals of each column the percentage of sales.
- the Percent of row function type, you will view in the totals of each column the percentage of sales to the total.
⑥ Show hidden fields, in case there are any, (e.g. Period). Right-click on empty space on pivot area to Display field list. Add fields to:
- Rows (Grouped data area),
- Filters (Fields area at the top of the Pivot screen)
- Data (Grand totals area. Applies only to numerical fields)
Additional options are available by right-clicking on the empty space, on pivot area.
⑦ Show Analysis filters for creating and applying custom filters that include logical conditions (AND, OR, NOT AND, NOT OR). Click on Apply to view the results and OK to close the design screen.
⑧Apply Expand line sums and select:
- Tree view, to display totals at the first grouping level.
- Line view, to display totals at the end of the first grouping level.
⑨ Save changes.
⑩ Print the data to one of the available printing options.
Pivot Analysis can be automatically activated for any Browser/List given:
- it includes at least one numeric field, which displays totals,
- and the Force pivot option is activated (Browser design)