3 Replies Latest reply: Dec 22, 2015 9:09 AM by Philip Hammond RSS

    How to derive open cases from a start date and an end date?

    Philip Hammond

      As a newcomer to Qlik I am struggling to get my head around how to show numbers of people/services who are active over time.

       

      I have a Fact table with people and the services they receive. Each service has a start date and an end date. Qlik can show the numbers of starters and the numbers of endings each month (from their start/end dates) but cannot show the current active service users over time.

       

      I understand this is because without explicit data to show activity on each day Qlik has nothing to go on. I already have master calendars to fill out white space for the dates mentioned but this does not allow for the time between start and end.

       

      I have experimented with using a second table with interval match to try and draw out those dates between as additional rows.

       

      However I cannot be doing this right as it crashes Qlik each time the script is reloaded.

       

      //TempActive:

      //LOAD

      //$(varMinDate) + Iterno()-1 As Numact,

      //Date($(varMinDate) + IterNo() -1) as TempDateact

      //AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxEndDate);  

       

       

      //MasterActive:

      //Load

      //TempDateact AS ActiveDates,

      //Resident TempActive

      //Order By TempDateact ASC;

      //Drop Table TempActiveCalendar;

       

       

      //left join (MasterActive)

      //IntervalMatch(ActiveDates)

      //Load

      //ActStartDate as ActiveStartDate,

      //ActEndDate as ActiveEndDate

      //Resident FACTS;

       

       

      //left join (MasterActiveCalendar)

      //Load

      //[Service User ID],

      //ActStartDate as ActiveStartDate,

      //ActEndDate as ActiveEndDate

      //Resident FACTS;

       

      Any views or ideas on how to resolve this preferably in the load script would be most welcome.

       

      Cheers,

       

      Phil

        • Re: How to derive open cases from a start date and an end date?
          Sundarakumar Kalaimani

          Hi Philip,

          I could not completly understand the issue, can you pls share some sample data set and the requirement.

           

          We could not understand what MasterActiveCalendar or TempActiveCalendar are, it would be easiler if you can share this app.

           

          The issue may be bec of this last join where the key is not defined (assuming MasterActiveCalendar and MasterActive are same)

           

          //left join (MasterActiveCalendar)

          //Load

          //[Service User ID],

          //ActStartDate as ActiveStartDate,

          //ActEndDate as ActiveEndDate

          //Resident FACTS;

           

           

          -Sundar

            • Re: How to derive open cases from a start date and an end date?
              Philip Hammond

              Ok I think this has really helped actually.

               

              What I think is working is the following:

               

              MasterActive:

              load

              [Service User ID],

              date(ActStartDate + Iterno()-1) As ActiveDate

              resident FACTS

              While ActStartDate + IterNo() -1 <= ActEndDate

               

              adapted from the link provided by swuehl above

               

              What I found difficult is the idea that Qlik loops to bring back the records anyway. So I do not need to define a loop which sets out the individual Service User ID's for each group of records.

               

              Hopefully this will now take a tables as follows:

              Service User IDActStartDateActEndDate
              100056201/07/201204/07/2012
              108945601/08/201409/08/2014
              20010004/05/201503/06/2015

               

              and create a second which has all the "missing" dates:

               

              Service User IDActiveDate
              100056201/07/2012
              100056202/07/2012
              100056203/07/2012
              100056204/07/2012
              108945601/08/2014
              108945602/08/2014

               

              It would seem like it creates about the right number of records but the analysis of this is looking a bit funny.

               

              Cheers,

               

              Phil