Lost Customers by Period

[Channa]

i have data by like below

for 2021 Feb A8 is missing i need to show count as 1

for march A7 and A9 missing i need to show count 2

i need script level solution 

YearMonth	ID
2021-Jan	A1
2021-Jan	A2
2021-Jan	A3
2021-Jan	A4
2021-Jan	A5
2021-Jan	A6
2021-Jan	A7
2021-Jan	A8
2021-Feb	A1
2021-Feb	A2
2021-Feb	A3
2021-Feb	A4
2021-Feb	A5
2021-Feb	A6
2021-Feb	A7
2021-Feb	A9
2021-Mar	A1
2021-Mar	A2
2021-Mar	A3
2021-Mar	A4
2021-Mar	A5
2021-Mar	A6
2021-Mar	A10
2021-Mar	A11

[NitinK7]

Try below code

ABC:
Load
YearMonth,
ID,
KeepChar(ID,'1,2,3,4,5,6,7,8,9,0') as ID1;

load * Inline [
YearMonth, ID
2021-Jan, A1
2021-Jan, A2
2021-Jan, A3
2021-Jan, A4
2021-Jan, A5
2021-Jan, A6
2021-Jan, A7
2021-Jan, A8
2021-Feb, A1
2021-Feb, A2
2021-Feb, A3
2021-Feb, A4
2021-Feb, A5
2021-Feb, A6
2021-Feb, A7
2021-Feb, A9
2021-Mar, A1
2021-Mar, A2
2021-Mar, A3
2021-Mar, A4
2021-Mar, A5
2021-Mar, A6
2021-Mar, A10
2021-Mar, A11
];

MinMaxNum:
Load
YearMonth,
min(ID1) as minID1,
max(ID1) as maxID1
Resident ABC
group by YearMonth;

Let totl_YearMonth=NoOfRows('MinMaxNum');

for i=0 to $(totl_YearMonth)-1

Let vMinID = Num(Peek('minID1', $(i), 'MinMaxNum'));
Let vMaxID = Num(Peek('maxID1', $(i), 'MinMaxNum'));
Let vYearMonth = Peek('YearMonth', $(i), 'MinMaxNum');

Temptab:
LOAD
'$(vYearMonth)' as YearMonth,
$(vMinID) + Iterno()-1 As Num,
Date($(vMinID) + IterNo() ) as TempNum
AutoGenerate 1 While $(vMinID) + IterNo() -1 < $(vMaxID);

Join(ABC)
Mastertab:
LOAD
YearMonth,
TempNum as ID1
Resident Temptab;

Next i

Drop Table Temptab;
drop Tables MinMaxNum;

Final:
Load
YearMonth,
Count(if(KeepChar(ID,'1,2,3,4,5,6,7,8,9,0')<>ID1,YearMonth)) as Counter
Resident ABC
Group by YearMonth;
exit Script;

[Channa]

For i = -1 to 12;

PreviousMonth:

load  

ID ,

date(Date#(Year&'-'&left(Month,3) ,'YYYY-MMM')) as YearMonth

FROM [lib://Revenue.qvd]

(qvd)

where date(Date#(Year&'-'&left(Month,3) ,'YYYY-MMM')) = AddMonths(YearStart(Today()),$(i))

;

PreviousMonthCount:

load 

 AddMonths(YearStart(Today()),$(i)+1) as YearMonth,

 count(DISTINCT ID) as CountofPreviousMonth resident PreviousMonth

 group By

 AddMonths(YearStart(Today()),$(i)+1)

;

CurrentMonth:

LOAD

    ID,

    Year,

    left(Month,3) as Month

FROM [lib://Revenue.qvd]

(qvd)

where  

Exists(MRRAccount.MRR__c)

and 

 date(Date#(Year&'-'&left(Month,3) ,'YYYY-MMM'))> AddMonths(YearStart(Today()),$(i))

and  date(Date#(Year&'-'&left(Month,3) ,'YYYY-MMM')) < AddMonths(Today(),$(i)+5)

;

inner join(PreviousMonthCount)

CurrentMonthCount:

load 

date(Date#(Year&'-'&Month ,'YYYY-MMM')) as YearMonth,

count(DISTINCT ID ) as CountofCurrentMonth resident CurrentMonth

group By

date(Date#(Year&'-'&Month ,'YYYY-MMM'))

;

Concatenate(NewAccounts)

Load 'Lost customers' as NewAccounts,

Date(YearMonth,'YYYY-MMM') as ContractSignedYearMonth,

-1*(CountofPreviousMonth-CountofCurrentMonth )as NumberOfNewAccounts resident PreviousMonthCount;

drop table PreviousMonth;

drop table CurrentMonth;

drop Table PreviousMonthCount;

Next;

This work for me for all year