Retention Rate of customers

Q&A
,
1

The logic for retention is sum(distinct customer_name per month)/ sum of all active customers ( from year start to that particular month)

The visual is a table grouped by job_completed_month .
I am using this logic to get the number of distinct customers from year start to a month:
distinctCountOver(
ifelse(
{job_completed_at} >= parseDate(‘2025-01-01’, ‘yyyy-MM-dd’) and
{job_completed_at} < addDateTime(1, ‘MM’, truncDate( ‘MM’, {job_completed_at}) )
and IN({customer_type}, [‘RETAIL’, ‘LOCAL’ ]),
{customer_name},
NULL
),
[{job_completed_month}], PRE_FILTER
)

But the issue is if a customer completed a job in January 28, it is getting included in January’s count but not Feb’s , I want that customer to be included in Feb count as well .
I think the logic is failing because of the group by but I am not able to work this out..
Any input is appreciated, Thanks

2

Hi @samp,

Can you try something like this?

distinct_count({customer_name}) / 
runningSum(distinct_count({customer_name}), [{job_completed_at} ASC])
3

@David_Wong , this also groups the data by that particular job_completed_ month .The expected and actual outputs are as below:

expected
ytd customers this_month_customers %
Jan-25 32 30 93
Feb-25 33 31 94
Mar-25 36 33 91
Apr-25 37 33 89
with the running total logoc
ytd customers this_month_customers %
Jan-25 31 31 100
Feb-25 62 31 50
Mar-25 95 33 34
Apr-25 128 33 25

I was able to work this with a sql logic and using calendar months instead of job_completed_month. Thanks for the suggestion.