Monday, February 25, 2019

Create a search that will show a count of customers created this month and also a count of customers inactivated the same month

To create a saved customer search which will show you the count of customers created this month and customers inactivated this month on the same search, follow the steps below:

1. Create a customer search, List > Search > Saved Searches > New > Customer.
2. On the Criteria Tab, mark Use Expressions = T

Criteria:
( | System Notes: Date is within this month | And
    System Notes: Type is Create | Or
    System Notes: Date is within this month | And
    System Notes: Field is Inactive | And
    System Notes: New Value is T | )

Results:
Formula (Numeric) | Sum | CASE WHEN (to_char({datecreated}, 'MM')) = to_char({today}, 'MM') THEN 1 ELSE 0 END
Formula (Numeric) | Sum | CASE WHEN {isinactive} = 'T' then 1 else 0 end

Note: You may change the summary label of the result fields to 1. Created within the Month and 2. Inactivated this Month. You may also replace {datecreated} on the formula depending on the field you're using.

No comments:

Post a Comment