Lost Business Beast Mode

SLam
SLam Member
in Beast Mode

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!

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • SLam
    SLam Member

    @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

Categories