1 Reply Latest reply: Nov 4, 2017 5:58 PM by Marco Wedel RSS

    Calculation number per month between start and end date

    Lutz Raasch

      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
          Marco Wedel



          one solution might be:




          LOAD acount,
          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))));
          LOAD acount,
              Date(Date_start+IterNo()-1) as Date
          Resident table1
          While Date_start+IterNo()-1 <= Date_end;
          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