Forum Discussion
Calculate Monthly persistency of customers
Hi, I am working on a client project and need to calculate the monthly persistence of each customer based on their purchase in those months. Problem statement: Medicine is subscribed to a patie...
itsmeanuj I believe the answer to your question lies in the quantity of medication consumed each month. With a 30-day month and a 14 day allowed lag, any given month that has a consumption of at least 16, qualifies as Persistency. By introducing a few helper columns, I calculated the monthly consumption (opening balance + purchased - consumed = closing balance), where the closing balance can not be less than zero.
The attached workbook demonstrates this is reproduces a Persistency table (in the green area) equal to the on in your example. But, perhaps I completely misunderstood. If so, please ignore.