Hi, anyone else has issues with sorting by co...

(Luis Saldaña) #1

Hi, anyone else has issues with sorting by column type Duration? I am using a virtual column type Duration for this: =Expire Date - Now()

and I want to see the result in ascending order, first the nearest to be expired but the App show the data in a unsorted way, for instance:

Duration (Ascending) 84:05:41 81:05:41 78:05:41 57:05:41 132:05:41 126:05:41

Thanks!

(Aleksi Alkio) #2

Just curious… what is the result if you use descending order?

(Luis Saldaña) #3

Yes, thanks @Aleksi_Alkio Translate

(Luis Saldaña) #4

Hi Aleksi In descending order the App shows:

102:02:03 105:02:03 108:02:03 125:02:04 131:02:04 56:02:04 77:02:04

Regards!

(Aleksi Alkio) #5

So… basically they are in correct order but not in correct way. First it sort the order with the 1st digit 1>1>1>1>1>5>6 and then with the 2nd digit like 0>0>0>2>3>6>7. It sound it’s sorting the order in the same way than text columns.

(Luis Saldaña) #6

It could be possible, anyway I expected something like this (Ascending):

57:05:41 78:05:41 81:05:41 84:05:41 126:05:41 132:05:41

(Aleksi Alkio) #7

Yes, it should work in that way. One workaround is if you calculate them as decimal in a virtual column.

(Suvrutt Gurjar) #8

As @Aleksi_Alkio has rightly guided , the sorting is happening digit wise instead of entire number value for the duration column or workaround is using decimal number format.

As another

workaround, my one request

to try is

to use Today() instead of NOW() in expression , if the

Expire Date is a Date type of

column and not DateTime type of column.

So the expression for the virtual column will be (HOUR([Expire Date]-Today()))/24 and the resulting column will be number type

column instead of duration type.

The result will be remaining days for the expiry date from today, if accuracy in terms of days is acceptable.

Since the

resulting column is in Number (of days)format ,necessary sorting can take place.

(Aleksi Alkio) #9

We talked about this internally and this feature will be added within few days if it’s not something difficult.

(Luis Saldaña) #10

Hi Guys, thank you both; As the @Suvrutt_Gurjar suggestions, I made some changes, but, instead to change the type of my Duration column, I added a new one (Decimal) and included the @Suvrutt_Gurjar’s formula, this new virutal column is hidden and is only used for sort the data, now, the result is this:

(Aleksi Alkio) #11

When the fix is released, you can then use your original way.