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