Date Bucketing in NAV

NAV has a nice feature (table) that holds period date information. This allows us to easily create reports that take any date range and create period based columns (or Rows or Sheets) by days, weeks, months, quarters or years. This is basically the same functionality used in the Budgets trendscape window in NAV where you can see the data by day, week, month, etc.

The resulting report will look like this:

Account Jan-2008 Feb-2008 Mar-2008
1000 45,679 38,945 52,955
1100 2,676 3,886 6,765
1200 49,623 56,109 59,980

The NAV date functionality enables the columns of Jan 2008 to Mar 2008. With the change of one filter, this same report could be run by weeks for the same 3 month period.

The date table has 3 important fields and a few others that are nice to have. They are: Period Start, Period End and Period Type. If you wanted to see all the Period Types of "Month" during the period of Jan 2008 to Dec 2008, then we would use an NL() Jet Reports function on the NAV Date table to do this. Here is the formula:

=NL("Columns","Date","Period Start","Period Type","Month","Period Start","01/01/2008..31/01/2008")

This means… create a column per Period Start entry, for only periods of type Month for the range of all of 2008. This returns only the start of the period and then another NL() Jet function is necessary to obtain the end of that period. Here is that formula:

=NL(,"Date","Period End","Period Type","Month","Period Start",C$2) (where C2 is a reference to the first formula listed above)

This gives you the end of the period that goes for one month from the start of the period obtained in the first formula.

See the attached sheet for the exact formulas and a mock up of the functionality. The NAV Date Formulas.xls can be printed and is for learning purposes and the NAV Date Example.xls is a base report with only this functionality. The nice thing is the example can be used as a base for any period bucketing type of report.

For additional help, see Jet Report's online help for Date functionality.


Please sign in to leave a comment.