Creating a Lotus Notes view column categorized by month

A common problem when creating a Lotus Notes view is designing a column that categorizes documents by month (or quarter). Many Lotus Notes developers have reinvented this wheel many times over, with varying degrees of success. In this tip, SearchDomino.com contributor Chuck Connell attempts to solve this problem once and for all.

A common problem when creating a Lotus Notes view is designing a column that categorizes documents by month (or quarter). Categorizing by individual date is simple -- you just change a plain date column into a categorized column. But categorizing by month can be a pain.

Many Lotus Notes developers have reinvented this wheel many times over, with varying degrees of success. My hope with this tip is to solve this problem once and for all, and I invite readers to re-use my downloadable solution.

The key problem is that months are numbers, which you usually convert to text to create a readable column. Unfortunately, when you sort the resulting text column, the months appear in the wrong order.

For example, if you convert the month numbers 11 and 4 to text you get "11" and "4." When Lotus Notes sorts these values, "11" appears before "4." Obviously this is incorrect, since November actually comes after April.

Another approach is to convert the month numbers to month names, so 3 becomes "March" and 4 becomes "April." But displaying this column also gives the wrong answer, since April appears before March when it should appear after.

The solution is to use a hidden sort column that "fixes" the sort order, and then create a readable column that shows the months as you want them to appear. You may want to download the sample and examine the view named View1 as I describe it.

The first column in the Lotus Notes view is a standard counting/totaling column, so that the view shows the number of documents in each month category. You can remove this column from your version if you do not want this feature.

The second column contains the code "@Year (Date1)*20 + @Month(Date1)" and is a hidden column. The reason for multiplying the year number by 20 is to establish a correct overall sorting order for all year and month combinations. The trick causes November 2003 to be assigned a sort order before January 2004, as you want.

The third column contains the code:

Months := "Jan":"Feb":"March":"April":"May":"June":"July":"Aug":
"Sept":"Oct":"Nov":"Dec";
MonthName := Months[@Month(Date1)];
MonthName + " " + @Text(@Year(Date1))

Lines 1 and 2 in the above code assign a readable name to each month number. Line 3 creates the content of the displayed view column. Even though the text in this column does not sort in the correct date order, this does not matter, because the hidden second column has put the documents in the right order.

To translate this example to another language, simply change the month names in Line 1 above.

To modify the view so it displays sorted quarters (three months together), just change the code above to something like this:

Quarters := "Q1":"Q1":"Q1":"Q2":"Q2":"Q2":"Q3":"Q3":
"Q3":"Q4":"Q4":"Q4";
QuarterName := Quarters[@Month(Date1)];
QuarterName + " " + @Text(@Year(Date1))

Notice that the code uses the square-bracket syntax to pick out list elements. Because of this, the code will not work on Lotus Notes versions prior to ND6, when this feature was introduced.

If you find a way to improve this design, or have a simpler alternative, or find any bugs(!), please let me know.

About the author: Chuck Connell is president of CHC-3 Consulting, which helps organizations with all aspects of Lotus Notes and Domino.

MEMBER FEEDBACK TO THIS TIP

Regarding the code:

@Year (Date1)*20 + @Month(Date1)

You can accomplish the same thing using the following code:

@Text(@Year(Date1)) +
@Right("0" + @Text(@Month(Date1));2)

The leading zero helps to correctly sort the single digit months.
—JT H.

******************************************

When it is acceptable to show the months numerically, a simpler approach can be adopted. Simply use the following formula:

@Year( @Created ) + ( @Month( @Created ) / 100 )

This will display as 2006.09, 2006.12, etc. It must be formatted numerically -- with two decimal places, to avoid October showing as 2006.1. The field can then be correctly sorted and categorized as needed. A mathematical approach to calculating the quarter, is to use the following formula:

@Round( ( @Month( @Created ) / 3 ) + 0.49 ).

This will return a 1, 2, 3 or 4.
—Jack S.

******************************************

We do have a categorizing facility by month, which is as follows (assuming "Date" is the field name in the document):

  1. The first column is categorized by year (optional). My formula is:
    @Year(Date)
    
  2. The second column is a "hidden column" and displays month number from the Date field. My formula is:
    @Month(Date)
    
  3. The third column is to display the month (e.g., Jan, Feb, Mar, etc.). My formula is:
    mth := @Month(Date);
    @Select(mth; "Jan"; "Feb"; "Mar"; "Apr"; "May"; 
    "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec")
    

So now the view is categorized by year (optional) and month.
—Bala Krishna C.

******************************************

Doing the two column compute does work, but it's not necessary. I like to use the following formula in the column that will contain my "by month category":

@Adjust(@Date(TheDateTime);0;0;-
@Day(TheDateTime)+1;0;0;0);

This formula adjusts whatever the DateTime value is to only be a date, and always be set to the same day -- the first day, but also keeps the month and year.

Once you've trimmed off the time and the day is seen as the same value in the column, let Lotus Notes do the work.

  • Set the column style to be "Date/Time."
  • Change the display to be "Custom" and turn off the "Time" display.
  • Set "Show" to be "Only month and year" or "Month only," depending on what you want to see.
  • Set "Month" to be either "mmm," for three letter month abbreviations, or "mmmm" for the month's full name.
  • Now, change the date separators to whatever you want (I usually just delete them) -- after that, you're done.

Lotus Notes is still working with a DateTime value, so it handles the sorting properly, and then conveniently converts the month so you don't have to.

Here is a screen shot of the column settings:

Figure

I also created a quick sample database. If you want to check it out, it's at http://www.malcolm.hostetler.com/notes/tcalsort.nsf.
—Malcolm H.

******************************************

For many of my views collecting measurements, I use this method for sorting by year, quarter and month. I do it all in the same view.

This view shows completed requests by year, quarter and month for measurement purposes.

In the first column is the Year (categorized):

@Year(DateCompleted)

In the second column is the quarter (categorized):

month := @Month(DateCompleted);
@If(month = 1:2:3;"1st Quarter";
month = 4:5:6;"2nd Quarter";
month = 7:8:9;"3rd Quarter";
month = 10:11:12;"4th Quarter";
null)

The third column uses the month number to get the month by name in correct order (categorized, ascending order):

 
month := @Month(DateCompleted);
@If(month = 1; "January";
month = 2; "February";
month = 3; "March";
month = 4; "April";
month = 5; "May";
month = 6; "June";
month = 7; "July";
month = 8; "August";
month = 9; "September";
month = 10; "October";
month = 11; "November";
month = 12; "December";
null)


—LInda T.

******************************************

Here's another variation of the categorized by month tip and a consolidation of some of the earlier comments with a twist or two added.

First, use a single categorized column with the following @Formula code:

date := DocActivity_timeOpened;
year := @Text(@Year(date));
month := @Right("0" + @Text(@Month(date)); 2);
monthName := @If(month="01";"January";  
month="02";"February";  month="03";"March";
month="04";"April";  month="05";"May";  month="06";"June";
month="07";"July";  month="08";"August";  
month="09";"September";
month="10";"October";  month="11";
"November"; "December");
@If(
        @IsTime(DocActivity_timeOpened);
year + "/" + month + " ~ " + monthName + " " + year;
"(No valid date recorded)"
)

You need only to replace "DocActivity_timeOpened" in the first statement and "DocActivity_timeOpened" inside the @If statement with your own date/time fields.
—Tony A.

******************************************

I regularly have to separate dates based on the fiscal year, which, in our case, extends beyond two calendar years. The Formula language code I use is:

y := @Year(DateField);
m := @Month(DateField);

@If(m < 7;  @Text(y-1) + "/" + 
@Text(y); @Text(y) + "/" + @Text(y+1))

If your fiscal year does not run from July through June, change the "7" to the number corresponding to the month in which your fiscal year begins. When placing these same dates into quarters, I use this Formula language code:

X := @Month(DateField);
@If( X >= 1 & X <= 3; "Q3"; X >= 4 
& X <= 6; "Q4"; X >= 7 & X <= 9; "Q1";
"Q2")

Similarly, change the values relating to quarter numbers so that they correspond with each other.

When placing the values in months, I use two columns. The first is hidden with the Formula language code:

x := @Month(DateField);
@If ( x > 6; x - 6 ;  x + 6)

Because these are in different calendar years, I have to adjust the month so that January displays after December.

When displaying the month name, I use the following Formula language code:

d := DateField;

@Select(@Month(D);"January";
"February";"March";"April";"May";
"June";"July";"August";"September";
"October";"November";"December")


—Mike M.

Do you have comments on this tip? Let us know.

Please let others know how useful this tip is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our tip contest and you could win a prize. 

This was first published in September 2006

Dig deeper on Lotus Notes 6

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchDataCenter

SearchExchange

SearchContentManagement

Close