Level-Aware Calculations - RICOH Software Information Center

Level-Aware Calculation - Aggregate (LAC-A) Functions

With LAC-A functions, you can specify at what level to group the computation. By adding an argument into an existing aggregate function, such as sum, max, or count, you can define a group-by level for the aggregation. The added level can be any dimension independent of the dimensions added to the visual. For example: sum(measure,[group_field_A]).

To use LAC-A functions, enter them directly in the editor, adding the aggregation levels as the second argument, between brackets. This example shows an aggregate function and a LAC-A function, for comparison:

Aggregate function: sum({Printed Pages})
LAC-A function: sum({Printed Pages}, [{Location Name},{Printer Name}])

The LAC-A results are computed with the level specified in the brackets and can be used as an operand of an aggregate function.

You can also create a dynamic LAC group key that adapts to the group-by fields from the visual by using the $visualDimensions parameter. The $visualDimensions parameter represents the fields added to the Group by field well in the current visual. These examples show how to dynamically add group keys to the visual dimensions or remove group keys from the visual dimensions:

LAC-A with dynamic-added group key: sum({Printed Pages}, [${visualDimensions},{Location Name},{Printer Name}])
This function calculates, before the visual-level aggregation is calculated, the sum of printed pages, grouping by location, printer, and any other fields in the Group by field well.
LAC-A with dynamic-removed group key: sum({Printed Pages}, [${visualDimensions},!{Location Name},!{Printer Name}])
This function calculates, before the visual-level aggregation is calculated, the sum of printed pages, grouping by the fields in the Group by field well, except location and printer.

You can specify either an added group key or a removed group key in a LAC expression, but not both.

LAC-A functions are supported for these aggregate functions: avg, count, distinct_count, max, median, min, percentile, percentileCont, percentileDisc, stdev, stdevp, sum, var, and varp.

LAC-A Examples

You can use LAC-A functions for these types of use cases:

Run calculations that are independent of the levels in the visual. For example, if you use the following calculation, the printed pages are aggregated only at the location level, but not across other dimensions in the visual.
```
sum({Printed Pages},[{Location Name}])
```
Run calculations for dimensions that are not included in the visual. For example, if you use the following function, you can calculate the average number of printed pages by location, even if the Location Name field is not included in the visual.
```
avg(sum({Printed Pages},[{Location Name}]))
```
Use LAC-A combined with other aggregate functions and LAC-W functions. There are two ways that you can nest LAC-A functions with other functions.
- You can write a nested syntax when you create a calculation. For example, the LAC-A function can be nested with a LAC-W function:
```
sum(avgOver({Printed Copies},[{Printer Name}],PRE_AGG),[{Location Name}])
```
- When you add a LAC-A function into a visual, the calculation can be combined with visual-level aggregate functions that you set in the field wells. For more information about changing the aggregation of fields in the visual, see Changing Field Aggregation.

LAC-A Limitations

These limitations apply to LAC-A functions:

LAC-A functions are supported for all additive and non-additive aggregate functions, such as sum, count, and percentile. LAC-A functions are not supported for conditional aggregate functions that end with if, such as sumif and countif, or for period aggregate functions that start with periodToDate, such as periodToDateSum and periodToDateMax.
Row-level and column-level totals are not currently supported for LAC-A functions in tables and pivot tables. When you add row-level or column-level totals to the chart, the total number shows as blank. Other non-LAC dimensions are not affected.
Nested LAC-A functions are not currently supported. A limited capability of LAC-A functions nested with regular aggregate functions and LAC-W functions are supported. For example, these functions are valid:
- Aggregation(LAC-A()). For example: max(sum({Printed Pages}, [{Location Name}]))
- LAC-A(LAC-W()). For example: sum(sumOver({Printed Pages},[{Printer Name}],PRE_AGG), [{Location Name}])
These functions are not valid:
- LAC-A(Aggregation()). For example: sum(max({Printed Pages}), [{Location Name}])
- LAC-A(LAC-A()). For example: sum(max({Printed Pages}, [{Location Name}]),[{Printer Type}])
- LAC-W(LAC-A()). For example: sumOver(sum({Printed Pages},[{Printer Name}]),[{Location Name}],PRE_AGG)

Level-Aware Calculation - Window (LAC-W) Functions

With LAC-W functions, you can specify the window or partition for computing the calculation. LAC-W functions are a group of window functions, such as sumOver, maxOver, or denseRank, that you can run at the prefilter or preaggregate level. For example: sumOver(measure,[partition_field_A],pre_agg).

LAC-W functions can help you answer these types of questions:

How many of the printers processed a number of jobs under a certain value? The count is used as a dimension rather than a metric in the visual.
How much is the contribution of each printer to the total printer throughput?
How are my printers grouped into cumulative throughput ranges? The grouping is used as a dimension rather than a metric.

For more complex questions, you can inject a calculation or filter before RICOH Supervisor gets to a specific point in the evaluation of your settings. To directly influence the results, you add a calculation level keyword to a table calculation. For more information on how queries are evaluated, see Order of Evaluation in Analyses.

These calculation levels are supported for LAC-W functions:

PRE_FILTER – Before applying the filters from the analysis, RICOH Supervisor evaluates prefilter calculations. Then it applies any configured filters on the prefilter calculations.
PRE_AGG – Before computing visual-level aggregations, RICOH Supervisor performs preaggregate calculations. Then it applies any configured filters on the preaggregate calculations. These operations happen before applying top and bottom N filters.

You can use the PRE_FILTER or PRE_AGG keyword as a parameter in these table calculation functions: avgOver, countOver, denseRank, distinctCountOver, minOver, maxOver, percentileRank, Rank, stdevOver, stdevpOver, sumOver, varOver, and varpOver.

When you specify a calculation level, you use an unaggregated measure in the function. For example, you can use countOver({Job ID}, [{Printer ID}], PRE_AGG). By using PRE_AGG, you specify that the countOver function executes at the preaggregate level.

By default, the first parameter for each function must be an aggregated measure. If you use either PRE_FILTER or PRE_AGG, you use a nonaggregated measure for the first parameter.

For LAC-W functions, the visual aggregation defaults to MIN to eliminate duplicates. To change the aggregation, click , the Options button, next to the field, then select Aggregate and one of the available aggregate functions.