Calculation number per month between start and end date

neapicture — Fri, 03 Nov 2017 14:27:07 GMT

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

Re: Calculation number per month between start and end date

MarcoWedel — Sat, 04 Nov 2017 21:58:02 GMT

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

topic Calculation number per month between start and end date in QlikView

Calculation number per month between start and end date

Re: Calculation number per month between start and end date