Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

neapicture
New Contributor III

Calculation number per month between start and end date

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

1 Reply

Re: Calculation number per month between start and end date

Hi,

one solution might be:

QlikCommunity_Thread_280412_Pic1.JPG

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

Community Browser