12 Replies Latest reply: Jul 25, 2017 10:48 AM by Ozzie Boeuf RSS

    Count of members across monthly or quarterly time intervals

    Ozzie Boeuf

      Hi,


      Enjoying learning about QLIK Sense and what it can do.


      I have a data set of 8,000 unique members with differing contract start and end periods. An example of 1 record follows:


      Member ID   Date Created   Date Ended

      902314252    27 Feb 2015   31 Mar 2017


      The intent is to produce a trend chart showing a total count of all members for the end of each month where the member contracts were 'active'.  Here a contract is considered active if the end of the month is between the two dates inclusive.


      Look forward to understanding more about how to deal with time intervals and your thoughts on how to solve this issue.


      Thanks,

      Ozzie

        • Re: Count of members across monthly or quarterly time intervals
          Sunny Talwar

          You can either use IntervalMatch or while Loops in the Script to create either all dates or monthends between your two dates (Date Created and Date Ended). One you do that, you should be very easily be able to use the new date field as the dimension and Count(DISTINCT [Member ID]) as your expression

          • Re: Count of members across monthly or quarterly time intervals
            omar bensalem

            Maybe sthing like this?

             

            // First, we created a calendar, and we'll later use the MonthEnd field in our chart.

            We'll also use the created Date field to match it with our interval

            Let varMinDate=num(MakeDate(2017,01,01));

            Let varMaxDate=num(MakeDate(2017,12,31));

               

            TempCalendar:

            LOAD

            $(varMinDate) + Iterno()-1 As Num,

            Date($(varMinDate) + IterNo() - 1) as TempDate

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

               

            MasterCalendar:

            Load

              TempDate AS Date,

              week(TempDate) As Week,

              Year(TempDate) As Year,

              Month(TempDate) As Month,

              Day(TempDate) As Day,

                MonthEnd(TempDate) as MonthEnd,

              Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

              WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;

             

             

            //This is your table, I just format the 2 fields as Dates

            table:

            load MemberID, date(Date#(DateCreated, 'DD MMM YYYY')) as created,

            date(Date#(DateEnded, 'DD MMM YYYY')) as ended

            ;

            load * Inline [

            MemberID,  DateCreated,  DateEnded

            1,    27 Feb 2017,  31 Mar 2017

            2,    27 Mar 2017,  31 Mar 2017

            3,    27 Jan 2017,  31 Mar 2017

            3,    27 Sep 2017,  31 Nov 2017

            4,    27 Nov 2017,  31 Dec 2017

            ];

             

             

            //This is the table to Match between the Date of our calendar and your interval (created and ended) fields.



            IntervalMatch (Date)

            Load distinct created, ended resident table;

             

             

            result:

            Capture.PNG

            See the attached app for more details

            • Re: Count of members across monthly or quarterly time intervals
              Ozzie Boeuf

              Hi Omar,

               

              Perfect, you have solved my problem.

               

              thank you kindly,

              Ozzie.

                • Re: Count of members across monthly or quarterly time intervals
                  omar bensalem

                  Glad to help.

                  Don't forget to close the thread then by makring the correct answer as so.

                    • Re: Count of members across monthly or quarterly time intervals
                      Ozzie Boeuf

                      Hi Omar,

                       

                      I've adapted your script solution in this thread to load an Excel file of existing data but it creates a synthetic key.  Are you able advise how to remove the synthetic key in the script please?

                       

                      The script in its current form is:

                       

                      Let varMinDate=num(MakeDate(2016,01,01));

                      Let varMaxDate=Today();

                           

                      TempCalendar: 

                      LOAD 

                      $(varMinDate) + Iterno()-1 As Num, 

                      Date($(varMinDate) + IterNo() - 1) as TempDate 

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

                           

                      MasterCalendar: 

                      Load 

                        TempDate AS Date, 

                        week(TempDate) As Week, 

                        Year(TempDate) As Year, 

                        Month(TempDate) As Month, 

                        Day(TempDate) As Day, 

                        date( MonthEnd(TempDate)-1) as MonthEnd,

                        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                        WeekDay(TempDate) as WeekDay 

                      Resident TempCalendar 

                      Order By TempDate ASC; 

                      Drop Table TempCalendar; 

                       

                       

                      table:

                      LOAD

                           Member_ID as MemberID,

                      "Date Commenced" as created,

                           "Cessation Date" as ended

                      FROM [lib://QSE Data/Volunteer Data.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                      IntervalMatch (MonthEnd)

                      Load distinct created, ended resident table;

                  • Re: Count of members across monthly or quarterly time intervals
                    Ozzie Boeuf

                    Can anyone else provide advice here?


                    Thanks,

                    Ozzie