GROUPDATE Function Explained

NOMAD/UltraQuest GROUPDATE Function Explained

The information below is from the UltraQuest and NOMAD Reference Manual in the "GROUPDATE Function" section of the "Functions and Operators" section of Chapter 5.

Sometimes, there is the need to group dates into categories. For example, you may have a database of sales orders that contains the sales date, and you wish to report the total sales by month, quarter, or year.

The GROUPDATE function converts a DATE value to the start of a specified period, to allow the grouping of dates.

GROUPDATE(date-expression [, period[*n]] [, start-date])

Period can be MONTHLY, QUARTERLY, or ANNUALLY.

For example, suppose you want to create a tabular report from a SALES database with the REVENUE each SALES_REP had for each quarter, and your SALES table includes a DATE_OF_SALE column. In NOMAD, you could use the following commands:

DEFINE SALE_QUARTER AS DATE'Qth, YYYY' = GROUPDATE(DATE_OF_SALE, QUARTERLY);
LIST BY SALES_REP ACROSS SALE_QUARTER SUM(REVENUE);

In UltraQuest Reporter, you can generate the defined column named SALE_QUARTER in the advanced Columns dialog by clicking on Add (x*y)+2, then building the Expression GROUPDATE(DATE_OF_SALE, QUARTERLY) with a Column format DATE'Qth, YYYY' and checking DEFINE with the Name SALE_QUARTER. Then you can build the request by: adding SALES_REP, SALE_QUARTER, and REVENUE in the Columns dialog; in the Sort dialog, clicking Down for SALES_REP and Across for SALE_QUARTER; and by applying the Sum function to REVENUE in the Summarize Columns dialog.

There are many other uses of the GROUPDATE function, such as reporting dates in fiscal years that differ from calendar years. For more examples of how GROUPDATE can be used, refer to the GROUPDATE section of the "Functions and Operators" section of Chapter 5 of the UltraQuest and NOMAD Reference Manual.

Next week's topic: DECODE Function