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

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

          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