Tips for Working with Aggregations

A useful technique for specifying ID variables 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 DATE-based or DATETIME-based columns that 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 requesting a descending rank on such a column simplifies the report task of reporting on the "last N time periods" (such as the past three weeks). For example, if WEEKDATE is an ID column in an aggregation that has DAYDATE in the class list, then 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 make use of this feature.)