Reverse sort order of dates

When you create a table in TARGIT, where dates (or Years, Months etc.) are added as a dimension on the vertical axis, the default sort order will be oldest dates on the first row and newest dates on the last row.

A common request among TARGIT users working with tables containing dates, is the ability to sort data in the reverse sort order: Newest dates as the first row and oldest dates as the last row.

The standard way of making end-user based sorting (by clicking the sort arrows in the column headers) will not work here, because this kind of sorting will treat dimension values as strings and simply make an alphabetically sort order. E.g., month names will be sorted like April, August, December, February ... etc.

As an example, we would like to make a reverse sort order of this table:

You will need to introduce a calculated column:

allcount(d1, d1:0(u), m1)

This calculation will make a column with a count number of your rows: 1, 2, 3 etc.

Notice the (u) modifier, meaning "make a count from the first row to the current row in the original unsorted order".

This modifier is important, as it will also enable us to use the calculated column for sorting. E.g., a reverse sort order on the calculated column will also produce the requested reversed sort order on dates:

As a Designer user, you may choose to hide the 'Sort order' calculation after you reversed the sorting.

Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.