How to filter a list of orders to get different lists of clients

1

Hi everybody,

I put a few questions in the same post here as the amazing support team suggested this can be of some interest for other people too.

How to filter a table ( a list of orders) to get a few different views all related to clients:

  1. Clients listed by week
  2. Clients that didn’t make any order last week
  3. Clients that made at least 2 orders this year
  4. Top ten clients by revenue
  5. Clients rank by average order

Using (almost) the same few formulas than we should be able to get some more lists related to products:

  • Quantity sold for each product
  • Rank of products by revenue
  • Products that sold more/less than n quantity
  • and so on

The table Orders has the following main colums:

Order nr, Order date, Client, Product, Quantity, Amount

(each row is a single Product, so the same “Order nr” can appear on several rows).

I added the column Week (week number) for the sake of semplicity.

None of the formulas here below worked, of course :slight_smile:

Thanks. Paolo

Question 1: Clients listed by week

For each week, a list of clients that made at least one order that week (if a client placed more than one order that week, that client should appear just once).

I grouped the view by week and tried the following table filter:

Order nr.Unique() AND Client.Unique()

Question 2: Clients that didn’t make any order last week

A list of clients that didn’t place an order at all last week.

E.g., last week Client 1 only ordered, so the filtered list should read: Client 2, Client 3.

I tried the following formula:

thisTable.Filter(Client, IsoWeekNumber(thisRow.Order date).Sort().Last()<IsoWeekNumber(Today()-7)).Unique()

Question 3: Clients that made at least 2 orders this year

List of clients that made 2 or more orders.

thistable.Filter(Client, Order nr.Unique().Count()>=2).Client

Question 4: Top ten clients

The sorted list should read the top ten clients by revenue.

Slice(thistable.Sort(false,(thistable.Filter(Client.Unique().Amount.Sum())), 1,2,3,4,5,6,7,8,9,10)

Question 5: Clients rank by average order

  1. Calculate the amount of each order
  2. Filter the orders of each client
  3. Calculate the average amount for the orders of each client
  4. Sort them

Now I should just translate that into a formula :slight_smile:
Here is the doc. Thanks again.

2

Great timing! I think most of your questions can be solved by extending your Clients table with an Orders lookup column using the Reference Lookup feature that just launched (see post).

Once you have that, questions like Top ten clients and Clients rank by average order should be straightforward filters and sorts on the Clients table.

Here’s a quick GIF of a couple steps of this sequence (focused on Question 5): gif

1 Like
3

Perfect! Thank you very much, shishir.
Very interesting post, by the way.