1

Sorting multiple rows by a group

Hi, I'm struggling to concisely explain my issue, so I haven't had luck searching existing posts. Apologies if this is a duplicate question. 

I have a table with a user-selection for start and end dates which filters a table. Each month has a 1-1 relationship with a record. So if you were filtering for Jun, Jul, Aug, you'd see:

Date column: Jun, Jul, Aug, Jun, Jul, Aug, etc

ID: ID1, ID1 ID1, ID2, ID2, ID2

Value: $U, $V, $W, $X, $Y, $Z

What I'm trying to do is keep that ordering in tact with the SKU column sorted so they remain together, but then order the whole table by the sum of column C. So in the example above, SKU2 sums to $2,645 while SKU 1 sums to $2,396, and so those bottom three rows with SKU2 should appear first in the table. 

Any help would be appreciated!

3 comments

  • 1
    Avatar
    Meggan King

    Hello,

    I'm not sure I fully understand your plans for sorting. Do you want to show the total by SKU as well? Or behind the scenes, check the total by SKU and that decides the sort order? 

     

    You can use Hidden Data to calculate the total by SKU, but will need to repeat the total per SKU type. So in your example the hidden data would be

    Date column: Jun, Jul, Aug, Jun, Jul, Aug, etc

    ID: ID1, ID1 ID1, ID2, ID2, ID2

    Value: $U, $V, $W, $X, $Y, $Z

    SKU Total:  $2,396,  $2,396,  $2,396, $2,645, $2,645, $2,645 

     

    You could then sort based on SKU Total, however, I'm not sure what order your dates will show up as. 

    An alternative would be to use a drill-down table. At the high level have ID, SKU Total, then drill in for the specifics per SKU. 

    Happy to offer more ideas, once I better understand how you want to display that total, or if it should stay hidden? 

    Thanks

    Meggan

  • 1
    Avatar
    Jonathan Garro

    Hi Meggan, 

    "Do you want to show the total by SKU as well? Or behind the scenes, check the total by SKU and that decides the sort order?" - The latter.

    I'm looking to basically sort by multiple rows. I think my case is similar to this one? https://support.klipfolio.com/hc/en-us/community/posts/360017957913-Groupby-multiple-columns-

    As in my original screenshot, you have a datasource with date, sku, and value. The user may select, say June and July to filter. Say there are just two SKUs, so four rows, June SKU 1, June SKU 2, July SKU 1, July SKU 2. I need to keep SKUs together by month, but then sort by the summed value of the value column. So if the sum of values for SKU 2 is higher than the sum of values for SKU 1, then those two would sit at the top of the table. Does that make sense?

  • 0
    Avatar
    Jonathan Garro

    Quick update on this - I created a hidden table below the one I need to display to perform the calculation. I tried to then use a lookup of the “SKU” in the top table to the “SKU Key” column in the bottom to pull that calculated value, but that column won’t show up in the references. I’m thinking that’s a circular reference issue? If so, is there a way to mirror the sort order of the skus in the bottom table to match those of the hidden one below? See screenshot.

    Here is how I calculated the value in the second table.

Please sign in to leave a comment.