Calculate Monthly persistency of customers

%3CLINGO-SUB%20id%3D%22lingo-sub-1362552%22%20slang%3D%22en-US%22%3ECalculate%20Monthly%20persistency%20of%20customers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1362552%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20client%20project%20and%20need%20to%20calculate%20the%20monthly%20persistence%20of%20each%20customer%20based%20on%20their%20purchase%20in%20those%20months.%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%20statement%3A%20Medicine%20is%20subscribed%20to%20a%20patient%20(1%20pill%20each%20day).%20Now%20based%20on%20his%20purchase%20of%20medicine%2C%20we%20need%20to%20calculate%20how%20persistent%20he%20is%20for%20the%20next%206%20months%20on%20medicine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20an%20ideal%20scenario%2C%20the%20patient%20should%20take%20medicine%20continuously%2C%20but%20he%20will%20still%20be%20called%20persistent%20unless%20there%20is%20a%20lag%20of%20more%20than%2014%20days%20(see%20row%204%20in%20the%20example%20below%20-%20he%20just%20purchased%20medicine%20for%2025%20days%20but%20his%20persistence%20is%201%20in%20M1%20since%20lag%20is%20less%20than%2014%20days).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHe%20can%20either%20purchase%20medicine%20every%20month%20(see%20row%204%20below)%20or%20in%20bulk%20for%20a%20few%20months%20together%20(see%20row%201%20below).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20we%20need%20to%20calculate%20Persistency%20for%20each%20month%20as%201%20or%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22846%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278%22%3EAllowed%20Lag%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E14%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETreatment%20Days%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EM1%3C%2FTD%3E%3CTD%3EM2%3C%2FTD%3E%3CTD%3EM3%3C%2FTD%3E%3CTD%3EM4%3C%2FTD%3E%3CTD%3EM5%3C%2FTD%3E%3CTD%3EM6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EP1%3C%2FTD%3E%3CTD%3EP2%3C%2FTD%3E%3CTD%3EP3%3C%2FTD%3E%3CTD%3EP4%3C%2FTD%3E%3CTD%3EP5%3C%2FTD%3E%3CTD%3EP6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1362552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1369047%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Monthly%20persistency%20of%20customers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1369047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657379%22%20target%3D%22_blank%22%3E%40itsmeanuj%3C%2FA%3E%26nbsp%3BI%20believe%20the%20answer%20to%20your%20question%20lies%20in%20the%20quantity%20of%20medication%20consumed%20each%20month.%20With%20a%2030-day%20month%20and%20a%2014%20day%20allowed%20lag%2C%20any%20given%20month%20that%20has%20a%20consumption%20of%20at%20least%2016%2C%20qualifies%20as%20Persistency.%20By%20introducing%20a%20few%20helper%20columns%2C%20I%20calculated%20the%20monthly%20consumption%20(opening%20balance%20%2B%20purchased%20-%20consumed%20%3D%20closing%20balance)%2C%20where%20the%20closing%20balance%20can%20not%20be%20less%20than%20zero.%3C%2FP%3E%3CP%3EThe%20attached%20workbook%20demonstrates%20this%20is%20reproduces%20a%20Persistency%20table%20(in%20the%20green%20area)%20equal%20to%20the%20on%20in%20your%20example.%20But%2C%20perhaps%20I%20completely%20misunderstood.%20If%20so%2C%20please%20ignore.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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 patient (1 pill each day). Now based on his purchase of medicine, we need to calculate how persistent he is for the next 6 months on medicine.

 

In an ideal scenario, the patient should take medicine continuously, but he will still be called persistent unless there is a lag of more than 14 days (see row 4 in the example below - he just purchased medicine for 25 days but his persistence is 1 in M1 since lag is less than 14 days).

 

He can either purchase medicine every month (see row 4 below) or in bulk for a few months together (see row 1 below). 

 

So we need to calculate Persistency for each month as 1 or 0.

 

      Allowed Lag14     
Treatment Days  
M1M2M3M4M5M6 P1P2P3P4P5P6
903012000 111100
300300300 101010
1 Reply
Highlighted

@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.