Quick Formulas allow users to quickly build mathematical and statistical logic on the selected items in a single step. The formula options exposed in the 'quick' menus are directed at the typical listing of functions users often need, and are driven by the type of members selected (dates, measures, normal hierarchy elements etc) and the number of elements selected (one, two or more). All quick formulas can be fully customized and edited using the Formulate tools - which allow the user to construct ANY type of mathematical operation possible within the given data model and language (PQL for SQL data sources and MDX for others).
Note: All quick formulas built directly in Discover are initially built as one-of, report specific items. However, they can be promoted a sharable item. See here for more.
The heuristic engine in Pyramid changes the types of quick formulas available based on what is selected. The selections can be made from the element trees, or directly from visuals.
These methods are classic mathematical operations for any element on both hierarchies and measures: for example, Absolute value:
These methods are designed to produce date time logic on elements from date-time hierarchies only. They will only work on non-year elements: for example, Year to date.
Note: The type of date functions exposed changes based on the hierarchy type. Therefore, the options for quarters are different to those for months or actual dates.
Note: Two extra functions are available for MDX data sources (like MS OLAP, Tabular and SAP BW): Parallel Period and Periods to Date
These methods are designed to produce hierarchical logic on element from regular, multilevel hierarchies only. For example Percentage of Parent.
These methods offer basic math operations using the two selected elements or measures. This includes Addition, Percentage Variance and Ratio.
Note: The custom measure calculation will be applied according to the order in which the items are selected. For example, to subtract Returns from Quantity, be sure to select Quantity first, and then Returns.
These methods offer statistical aggregation logic using the RANGE of elements BETWEEN the two selected items. This means that picking the "average" range aggregation using January and June will build a formula of the average across all the months between January and June inclusive.
These methods offer statistical aggregation logic using the selected elements only. This means that picking the "average" aggregation using January and June will build a formula of the average across January and June only.
Notes: There are no special date-time formulas when using two or more date time elements.
Note: There are no aggregation function options for measures apart from "SUM".
Once more than two elements have been chosen, the only quick calc options available are "aggregate functions" - described above. Further, the only measure based function is a simple "Sum" operation.
Building Quick Formulas
To build quick calculations start by making selections of the relevant elements. Then right click, and choose the relevant calculation from the context menus. See examples and the below and the attached video for more.
- Click here to see a demo of how to build formulas.
Managing Quick Formulas
Formulas created from the quick menus can be fully edited, deleted and shared like any other formula item in the system. Click here to read more on how to manage quick formulas.
To build a quick formula from the Elements panels, Ctrl + click on the elements (without selecting their checkboxes ) that you want your calculation to include.
Right click on one of the selected elements, and choose the required calculation: custom member formula.
From a grid visual (or any visual), select the relevant member elements (as described here). Right click on the selection and from the Quick Calc sub-menu, create a formula:
Quick custom measure calculations can be configured in a few ways:
Ctrl + click and select the required measures from the Measures tree panel. Right click and select Create Measure (Sum). The number of calculation options will be larger if it is only a single measure.
From a grid visualizationuse the selection tools to select the required measures, then right click and choose a calculation from the context menu.
From a chart visualization, right click on the relevant measure label. From the context menu, click Create Measure.: