How can we help?

New post
1

Table Column Sort

Avatar
Phil Pare
None
Follow

Add the ability to build a custom sort order. instead of the options being A-Z and Z-A.  Display list data and select in which order to display it.

Date Votes

8 comments

  • Avatar
    Janice Janczyn Official comment

    Hello,

    One way to do a custom sort is to:

    1. Create a small "mapping" file with 2 columns that maps your custom values to a number that indicates the order you want and upload it as a datasource, e.g.,

           A          B

         X-Small      1

         Small          2

         Medium      3

         Large         4

         X-Large      5

      2. Then use SORT( LOOKUP( data, A:A, B:B), ascending numeric, data ) where data points to the data you want to SORT.


    Please let us know if this doesn't address your scenario, in which case, please also provide a few more details so we can help.

     

    Thank you,

              Janice

  • 0
    Avatar
    Nikta Kanuka

    Hi Phil,

    Thanks for the suggestion! I want to make sure I'm understanding your request correctly.

    It sounds like you want to specify the order of list. So for example, if the data is:

    Medium
    Medium
    X-Large
    Large
    Small
    Small
    Medium
    X-Small

    You want the data to appear in the order: X-Small, Small, Medium, Large, X-Large. Right?

    I will pass this idea along to our Product Manager. In the meantime, you should be able to get the desired results by using an ARRAY and LOOKUP function. 

  • 0
    Avatar
    Gal Zohar

    @nikta

     

    Can you show how using an Array and Lookup addresses this requirement please?

  • 0
    Avatar
    Gal Zohar

    Thank you Janice,

     

    I feel like I am getting very close, but not quite there yet.

     

    I uploaded an additional data source for the mapping.

    The main column of my table now looks correct:

    SORT(LOOKUP(GROUP(@A:A;),@A:A;,@B:B;),"ascnumeric",GROUP(@A:A;))

    The first and last @A:A are my actual data, and the second one is column A from the mapping file.

    Note that I needed grouping as well as I have multiple "Medium" items and I want only one to show up with a sum of all medium items.

     

    So my next challenge is now to match the next column (let's say it's the sum of number of shorts sold).

    The formula I had there was:

    GROUPBY(@A:A;, IF(@B:B;="AMER",@C:C;,0))

    (sum up all the "AMER" shirts, per size)

    But now the data is skewed. How do I match this GROUPBY to the sorted values in the first column?

     

    I hope my question is clear! Thanks for all your help so far.

  • 0
    Avatar
    Janice Janczyn

    Hi Gal,

    To align your # of shorts sold column with the first column requires a LOOKUP, where !column1 is a REF to your first column

    LOOKUP( !column1,

                     GROUP( SELECT( @A:A, @B:B="AMER")),

                     COUNTDISTINCT( SELECT( @A:A, @B:B="AMER")) )

    If this isn't clear or doesn't address your question fully, send a ticket to support@klipfolio.com.

    Thanks,

             Janice

     

  • 0
    Avatar
    Stefan stefan

    Hi there, I have a similar requirement. I have a table with a column that contains (to save some space) a concatenation of 2 things result being s.th. like 23 (187), which means 23 this month, 187 for all time. Now, when I sort, I'd like to simply sort by the column "this month". but because it's alphabetical according to the concatenated string, it won't work. It would be nice, if this could be added! Thanks!

  • 0
    Avatar
    Utkarsh Gupta

    Hi,

     

    Is there any way to disable sort (by clicking on the header) in a table?

     

    Thanks

    Utkarsh

  • 0
    Avatar
    Janice Janczyn

    Hi Utkarsh, 

    No, there is no way to disable the sort by clicking a table header.

    Thanks,
              Janice

Please sign in to leave a comment.