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:
- Clients listed by week
- Clients that didn’t make any order last week
- Clients that made at least 2 orders this year
- Top ten clients by revenue
- 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
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.
Question 5: Clients rank by average order
- Calculate the amount of each order
- Filter the orders of each client
- Calculate the average amount for the orders of each client
- Sort them
Now I should just translate that into a formula
Here is the doc. Thanks again.