Toplist of orders by customers - how to create a collection on app-start?

[Oskarkuus]

I want to create a collection on app-start contianing the top 5 customers with most orders based on my order-table.

order table have column OrderName and Lookup column Customer (lookup to table Customers)

I can do this: FirstN(Distinct(Order-Table;Customer);5)
If i put this in a gallery i get the 5 rows... but i want the top 5 with the most orders and i want to Sort the table so the one with the most orders are at the top.

How can i do this?

[BCBuizer]

Hi @Oskarkuus,

I assume your order table just has order items in it and no count of orders.

The below creates a table that has the distinct Customer names and Count of orders in your order-table. This is then sorted by count and finally only the first 5 are displayed:

FirstN(
	Sort(
		AddColumns(
			Distinct(
				Order-Table;
				Customer
			);
			"Count";
			CountRows(
				GroupBy(
					Order-table;
					"Customer";
					"Temp"
				)
			)
		); 
	Count
	); 
	5
)

[Oskarkuus]

I dont really get it.

These columns i dont understand how to adapt to my tables.

Since Customer is a lookup column i dont think i can use that in that sense , correct?

[BCBuizer · Accepted solution]

Hi @Oskarkuus ,

Indeed this will not work for a LookUp column as such, so another column can be added that has the customer.value:

FirstN(
    Sort(
        AddColumns(
            GroupBy(
                AddColumns(
                    Order-Table;
                    "_Customer";
                    Customer.Value
                ),
                "_Customer";
                "CustomerGroups"
            ),
            "Count";
            CountRows(CustomerGroups)
        );
        Count;
        Descending
    );
    5
)