Flag Gained and Lost Customers by Monthly occurrence

Q&A
, ,
1

Hey QS experts,
I have a scenario where this is the ask-“Identify Gained and Lost Customers using the conditions as below.” The Data set Range is from Apr 2025 to Nov 2025 -
Gained:

  • All Customers occurring in April 2025 will be tagged as new by default.
  • In subsequent months they will show “gained” only if they are missing from the previous Month.
    The logic for New is simple and I was able to achieve it using Lag function. But I need help with identifying ‘Lost’ customers as below -

Lost: If Customers do not show in the succeeding month then they will be flagged as “Lost” but this will not apply to Nov2025 as there is no data for December 2025

image
image471×340 4.66 KB

I’m trying to use Lead function to achieve this but when I add additional condition to exclude End date i.e. 30Nov2025, I get the custom aggregates error.
(**Max_date is calculated as 30Nov2025 from the data)
Calculation:
ifelse({EntryDate} <>{Max_date},
ifelse(
isNull(lead(Customer, [{EntryDate} ASC], 1, [Customer])),
‘Lost’,
‘n/A’
),‘n/A’)

Please advise!

2

Hello @Ops_Expert

I believe you are getting the mismatch aggregation error because you are comparing Max Date against an unaggregated value Entry Date.

Can you share how you are creating Max Date i.e. are you using Entry Date with max() or are you doing something else?

3

This is how I’m using but it won’t allow due to mismatched agg error-

ifelse(maxOver({EntryDate},,PRE_FILTER)<>{EntryDate},ifelse(
isNull(lead(Customer, [{EntryDate} ASC], 1, [Customer])),
‘LOST’,
‘NA’
),‘NA’)

4

Found a fix by adding a new field for taking consecutive month difference and then adding an ifelse statement with the 2 calculations