Sorted!

An easy and quick tip to sort out the order of time.

When the gods invented time they were not considering the Manager running his analytics reports!

It’s a simple question that the manager asks; Give me sales figures by month.

The answer should look like this

January 10
February 11
March 13
April 13
May 15
June 19
July 22
August 35
September 22
October 12
November 11
December 10

But we ‘sorted’ by month and here’s the results L

April 13
August 35
December 10
February 11
January 10
July 22
June 19
March 13
May 15
November 11
October 12
September 22

Which is perfectly understandable really but not very readable.

So how do we get the proper results?

In the source table (normally W_DAY_D) where the month names are held there is normally a month number too (mth_num). Obviously this loops from 1 to 12 for each year. We will need the field containing the month number to be included as a logical column in the logical table where the month name is sourced from in the report. You’ll probably call this field ‘Month Number’!.
Now edit the ‘Month Name’ logical column. On the General tab there is a ‘Sort by’ box. Use the ‘Set’ button to find and select the ‘Month Number’ field.

Now whenever analytics has a query that include the Month Name field it will automatically add the Month Number field and add an ORDER BY month_number clause.

You must be careful when adding the sort functionality into a logical column. The field used must be at the same ‘level’ (for example, you cannot use date as the sort order for a month).

The technique is useful for Day Names too (using Day of Week Number) and could even be used for non time related data (e.g. Brand names have to be in a certain order).