Calculating KPI based on one Date.

[WhatsUp]

Hello together,

I have the requirement to calculate churn rates based on the number of customers who churned / the average number of customers. 

My goal is that a churn year is selected and based on that year the kpi is calculated for each month. My approach right now is the following:

Count({<churnDate = {*}>} CustomerId)
/ ( Count(Total {<churnDate = {*}>} CustomerId) / count (TOTAL distinct Year-Month churnDate )

The Problem is the first and second expressions are the same. The statement

Count(Total {<churnDate = {*}>} CustomerId) doesn't work because I'm looking for customers who didn't churn but average customers amount. I thought about something like this:

count({<CustomerId= {"=SelectedDate >= startdate AND SelectedDate <= enddate"}>} CustomerId))

where startdate and enddate is the period where a customer was active. But it still doesn't work since I'm selecting the Churn Year in the filter and it still only uses the customers where the churn date is in the selected churn year. 

Could someone help me with this? Is there a way to get the customer amount who churned and the average customer amount for only one selected date field, preferably the churn date?

[vinieme12]

Customers who didn't churn =  Count( {<CustomerId = {"=len(chunDate)<1"}>} Distinct CustomerId)

Customers who churned =  Count( {<CustomerId = {"=len(chunDate)>0"}>} Distinct CustomerId) 

Average Customers = what is the definition for Average Customer??

[WhatsUp]

Hi Vinieme, 

Thanks so far! For the average:

If we select a year for the churn it should divide by 12. If we select 2 years it should divide by 24. So the monthly average you could say

Kind regards

[MayilVahanan]

Hi

You can divide the values by monthyear field, depends on ur requirement.
Try like below

Count( {<CustomerId = {"=len(chunDate)<1"}>} Distinct CustomerId) / Count(Distinct MonthYear)
or
Count( {<CustomerId = {"=len(chunDate)<1"}>} Distinct CustomerId) / Count({<CustomerId = {"=len(chunDate)<1"}>}Distinct MonthYear)

[Chanty4u]

Try this

Count({<ChurnYear = {$(=Max(ChurnYear))}, ChurnMonth = {"<=$(=Max(ChurnMonth))"}>} DISTINCT CustomerId)

Count({<ChurnYear = {$(=Max(ChurnYear))}, ChurnMonth = {"<=$(=Max(ChurnMonth))"}>} DISTINCT CustomerId) /

Count({<ChurnYear = {$(=Max(ChurnYear))}, ChurnMonth = {"<=$(=Max(ChurnMonth))"}>} DISTINCT Total CustomerId)