Lost Business Beast Mode

SLam

Hello, I am trying to figure out if there is a way to identify clients that we no longer have business with.

Scenario: Client A and B has had closed orders with us in 2018-2020 us. Client A has had multiple orders in 2021, 2022, and 2023. Client B has no more orders after 2020. Is there a way to output Client B's information on a report?

There is a Closed Order Date field and I think we would need a way to compare it against itself.

Thank you!

GrantSmith

You can filter based on the max closed date

CASE WHEN MAX(`closed_date`) FIXED (BY client_id) < CURRENT_DATE() - INTERVAL 2 YEAR THEN 'Stale' ELSE 'Active' END

this will find the max date for each client and allow you to filter for stale values

ArborRose

Sure. Create a card with a filter. Or create a report to identify clients with whom you no longer have business based on their order history.

MAX([Order Date]) OVER (PARTITION BY [ClientID]) as Latest_Order_Date

Filter Clients with No Recent Orders: Once you have the latest order date for each client, you can filter the dataset to include only clients who haven't placed any orders after a certain date. For example, if you want to identify clients who haven't placed any orders since 2020, you would filter the dataset where the Latest_Order_Date is before 2021.

SLam

@GrantSmith Hello Grant, thank you for the suggestion. I tried using your method, but when I try to filter my beast mode, there are no results. I used the following:

CASE WHEN MAX(Order Close Date) FIXED (BY ClientID) < CURRENT_DATE() - INTERVAL 2 YEAR THEN 'Stale' ELSE 'Active' END