Way to sort by hidden field

luoqianhe

Is there a way to sort a table by a field that isn't in the table columns? I'm trying to sort time information chronologically like this:

1:00 AM

2:00 AM

…

12:00 PM

1:00 PM

But Domo sorts it like this:

1:00 AM

1:00 PM

2:00 AM

2:00 PM

etc.

I can have the time displayed as 24-hour but I'd rather not if possible.

GrantSmith

Domo is doing string sorting and not numerical sorting because you value is a string. You can create a beast mode to calculate the numerical hour value (if you don't already have it) and then use that in the sorting section instead of your actual display hour string you currently are using. This way it will display how you want with the correct format but be sorted numerically.

luoqianhe

The field I'm sorting by is a BM calculation: TIME_FORMAT('fieldname', '%h:%i, %p'). Does that return a string value?

GrantSmith

It does. If fieldname is a timestamp you can just do this to get the numerical equivalent

HOUR(`fieldname`)

luoqianhe

Yeah, but I want the time in the format I posted earlier, like 1:24 PM. Just the hour isn't enough. I can create a BM field with HOUR('fieldname') and sort by it to get it in the right order, but I don't want that field to appear in the table since it'd be redundant.

GrantSmith

That's fine, you'd just need two different beast modes, one for display and one for sorting. Have the display on your table and the sort in your sorting options on the card.

luoqianhe

How do I do that? I have a current field I'm sorting by but I can't seem to remove it.