You can sort data by values (smallest to largest, and largest to smallest) and members (A to Z, and Z to A). If you sort the values of one measure (for example, sales volume) from smallest to largest, the other cells in your crosstab are also affected by the sorting. The metadata therefore remains correct in the crosstab.
You can also sort a measure based on a dimension. For example, perhaps you want to sort sales volume based on the sales volume values for the most recent calendar year. It helps you to understand your data better as you can visualize and organize different views on the data in your crosstab. By sorting data, you are able to quickly identify the data you need for your decisions.
The figure Filtering shows how you can filter in Analysis for Office.
By selecting and removing members of your analysis, you can create new views of your data. You can filter members of a flat dimension with the filter dialog or directly on the crosstab. You can also filter members of a hierarchical dimension (hierarchy).
For example, if you analyze the sales volume of your company in the last three years, and you only want to see the values for the last three months of each year, you can remove the other months from your analysis.
Filtering members is a static action. Members that you have removed from your analysis remain excluded unless you change the filter criteria and include them again.
Member filters affect the totals and subtotals in your analysis. Only the values for the displayed members are included in the totals.
Parameters for Filter by Measure
With filtering measures, you can define rules to filter the data of your analysis to your current scope of interest. You can apply one or multiple rules to a measure. Depending on where you open the Filter by Measure dialog box, the filter definition is applied to a selected dimension, to all dimensions, or to the most detailed dimension in your analysis. The applied measure filters do not affect totals or subtotals in your analysis.
You can define rules to get a ranked list of data or a list of data matching a threshold value.
In a ranked list filtering, all entries of the currently displayed data are considered and their relationship to each other determines whether an entry is displayed. For example, you can define that the top three entries for a measure should be displayed.
In a threshold list filtering, an entry is filtered independently of the other entries if its value matches the criteria in the defined rule. For example, you can define that all entries are displayed that are greater than a specific threshold value.
Filtering measures is a dynamic action. Whenever you change the view of your data, the filter is applied again. For example, if you add a top five filter, five members are shown in your analysis. If you then add members that you previously removed from the analysis, some of these newly added members could match the top five criteria and replace some of the previously displayed members.
For simple calculations, you use two or more available measures as operands, and an operator to create a new measure. For example, you can create a new measure that is the sum of two available measures.
The figure Add a Calculation illustrates how you can add a simple calculation in the workbook.
- Dynamic calculations are not available for SAC connections. These include, moving minimum value, accumulative sum, and rank number.
- In Analysis for Office add-in, there are specific SAP functions to support Excel functionalities like Header information. Specific SAC Functions for cloud functionalities are not supported.
You can use analysis-specific advanced formulas to enhance the appearance of your workbook.
To find the other formulas you can use, choose the Insert Formula (fx) icon in Microsoft Excel.
Different formulas have different parameters you have to fill out. For example, the linked datasource or the dimension involved.
The SAP-specific formula only uses technical names for all function arguments. It is easy to find them if you switch the view of the Display Design panel to Show Technical Names.