Way to sort by hidden field

luoqianhe
luoqianhe Member
in Charting

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.

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • luoqianhe
    luoqianhe Member

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

  • GrantSmith
    GrantSmith Coach

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

    HOUR(`fieldname`)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • luoqianhe
    luoqianhe Member

    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
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • luoqianhe
    luoqianhe Member

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

Categories