Calculation number per month between start and end date

[neapicture]

Hello
I have a file with Acount, Type, Start Date and End Date.

It should calculate the number of any date, how many accounts fall within the start date and end date.

Count only accounts with type = 1 and accounts, which start with 2.
I have shown in the appendix for better understanding and the result.

The Dimmension is the month of the selected year. It is always calculated on a key date because the file changes every month

Thank you for your help in advance

[MarcoWedel]

Hi,

one solution might be:

table1:
LOAD acount,
    typ,
    Date_start,
    Date_end
FROM [https://community.qlik.com/servlet/JiveServlet/download/1374887-302367/test3.xls] (biff, embedded labels, table is Sheet1$, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 28)),Remove(Row, Pos(Top, 27)),Remove(Row, Pos(Top, 26)),Remove(Row, Pos(Top, 25)),Remove(Row, Pos(Top, 24)),Remove(Row, Pos(Top, 23)),Remove(Row, Pos(Top, 22)),Remove(Row, Pos(Top, 21)),Remove(Row, Pos(Top, 20)),Remove(Row, Pos(Top, 19)),Remove(Row, Pos(Top, 18)),Remove(Row, Pos(Top, 17)),Remove(Row, Pos(Top, 16)),Remove(Row, Pos(Top, 15))));

tabDateLink:
LOAD acount,
    Date(Date_start+IterNo()-1) as Date
Resident table1
While Date_start+IterNo()-1 <= Date_end;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2015) as MinDate, 
    MakeDate(2020)-1 as MaxDate 
AutoGenerate 1;

Right Join (tabDateLink) LOAD Date Resident tabCalendar;

hope this helps

regards

Marco