SORT_BY_KEY is not arranging numbers by value

Question
1

I have this object. SORT_BY_KEY doesnt seem to arrange the numbers in order. Is there anything I’m missing? I’m trying to quantity_sold by the key quantity_key.

SORT_BY_KEY(GROUP(data.SalesRecordsAll, item.name, {name_key:key , name:items[0].name, quantity_key:key, quantity:items[0].quantity_sold }), “quantity”)

2

Your formula should most likely be like:
SORT_BY_KEY(GROUP(data.SalesRecordsAll, item.name, {name_key: key , name: items.name, quantity_key: key, quantity: items.quantity_sold }), "quantity" )

3

Thanks. I’m using the group formula to group the collection respecting a certain field so this formula isn’t working.

4

What is your goal to be clear on how to try to solve this issue?

SORT_BY_KEY(
   GROUP(data.SalesRecordsAll, item.name, 
      {
         name_key: key, 
         name: items[0].name, 
         quantity_key: key, 
         quantity: items[0].quantity_sold 
      }), 
“quantity”)

What is the “name_key” and “quantity_key”? Why do you need them?
When you group your items with the GROUP() formula, you get the “item.name” as the “key”, so in your case it would be like this:

ORDER<grouped>(GROUP(data.SalesRecordsAll, item.name, {name: key, quantity: COUNT(items)}), grouped.quantity)

Or if your sales data is not the count of the grouping, but rather a sum of the “quantity_sold” properties of the grouped items, then:

ORDER<grouped>(GROUP(data.SalesRecordsAll, item.name, {name: key, quantity: SUM_BY_KEY(items, "quantity_sold")}), grouped.quantity)

I am really not sure what is your business logic behind it, but one thing I know… Referring to items[0] will always find you the first item in the grouped list. Which in most cases is not the solution people are trying to achieve.

5

Hey thanks for your reply. I’ll try your suggession.

Here’s the background. So I have a collection of sales with products (which I’m calling name) being sold and appear as a list in the collection so some appear more than once if sold many times. The formula there groups the collection as name (product). This is the GROUP formula.

GROUP(data.SalesRecordsAll, item.name, { name_key: key, name: items[0].name, quantity_key: key, quantity: items[0].quantity_sold })

Now to get the total quantity for each grouped name using the SUM function. To make sure it sums only each name seperately I’m using the repeat formula.

SUM(MAP(SELECT(data.SalesRecordsAll , item.name == repeated.current.name) , NUMBER(item.quantity_sold)))

My goal is to make sure the highest sales appear on the top of the list decending to the lowest at the bottom using the ORDER function like this.

ORDER(GROUP(data.SalesRecordsAll, item.name, { name_key: key, name: items[0].name, quantity_key: key, quantity: items[0].quantity_sold }), item.quantity, “desc”)

In a nutshell I want the name to appear once but should include the total for every sale. That list should have the largest sales at the top.

I really hope my English is clear enough.

6

This should be the way to go then. Just try this formula and let me know if it helps. You basically don’t have to do any modifications to it. Just copy-paste. :sweat_smile:

This contains your MAP() formula in itself. There is no need to do that, as the GROUP() formula is powerful enough to give you the option to sum up the count of items that are grouped under a single name.

7

Works like magic. I’m forever grateful. You’ve changed my life :sweat_smile:

Thank you so much.

8

Works perfectly well for the COUNT function. SUM_BY_KEY function isn’t working.