Tips for Working with Aggregations

A useful technique for specifying ID columns is to choose columns that are based on or determined by the value of a column that is already in the class list. An example of this is to choose columns that are based on the DATE or DATETIME fields. These columns are less granular than the aging column.

For example, consider the case where you have an aggregation that is aged according to the DAYDATE column, which records daily date values. The columns WEEKDATE and MONTHDATE are excellent candidates for use as ID columns because they are (essentially) determined by the value of DAYDATE. Thus, when creating reports, you can easily determine the days that belong with a particular week.

The same is true for other types of date or datetime-based columns that the user might create, such as a column that represents the calendar quarter or accounting period.

This tip is a corollary to choosing ID columns based on date, or datetime values. If you have such columns in the class or ID list, then request a descending rank on the column. This action simplifies the report task of reporting on the "last N time periods" (such as the past three weeks). For example, suppose WEEKDATE is an ID column in an aggregation that has DAYDATE in the class list. In that case, a descending rank on WEEKDATE facilitates reporting on the past three weeks of daily values. The report simply needs to filter where WeekDateRankDescending >= 3. (All the supplied aggregations and information maps use this feature.)