8 Replies Latest reply: Nov 4, 2016 3:57 AM by Aaron Xuereb RSS

    Counting Days in Specific Month between Two Dates

    Aaron Xuereb

      Hello,

       

      I am trying to create set analysis that will calculate the the number of days that fall within a certain month.


      Capture.PNG


      Any help would be greatly appreciated!


      Thx

        • Re: Counting Days in Specific Month between Two Dates
          Marco Wedel

          What's the use case here?

          Can you describe your data, charts, expression or upload a small sample application?

           

          thanks

           

          regards

           

          Marco

            • Re: Counting Days in Specific Month between Two Dates
              Aaron Xuereb

              Hi Marco,

               

              Thanks for your reply.

               

              I have a table with holiday apartment booking details and I would like to have a table in QlikView showing Occupancy % by month per apartment. So for each month I need a percentage of nights booked over the total for that month.

               

              Hope this gives you a bit more clarity.

               

              Regards

              Aaron

                • Re: Counting Days in Specific Month between Two Dates
                  Marco Wedel

                  Hi,

                   

                  one way to do so might be:

                   

                  QlikCommunity_Thread_238292_Pic2.JPG

                  QlikCommunity_Thread_238292_Pic1.JPG

                   

                  tabBookings:
                  LOAD RecNo() as BookID, *
                  Inline [
                      AptID, Start, End
                      A, 01/02/2009, 01/02/2009
                      A, 01/16/2009, 03/27/2009
                      A, 04/25/2009, 07/12/2009
                      B, 02/03/2009, 03/11/2009
                      B, 03/18/2009, 08/21/2009
                  ];
                  
                  tabMonthOccup:
                  LOAD *,
                      Num(DaysBooked/DaysInMonth,'0%') as Occupancy;
                  LOAD *,
                      MonthName(MonthBookStart) as MonthBook,
                      MonthBookEnd-MonthBookStart+1 as DaysBooked,
                      MonthStart(MonthBookStart,1)-MonthStart(MonthBookStart) as DaysInMonth;
                  LOAD BookID,
                      Date(RangeMax(Start,MonthStart(Start,IterNo()-1))) as MonthBookStart,
                      Date(RangeMin(End  ,MonthStart(Start,IterNo())-1)) as MonthBookEnd
                  Resident tabBookings
                  While MonthStart(Start,IterNo()-1)<=End;
                  

                   

                  I would suggest to go with John's solution however, as it should deliver a far more flexible result especially when adding some additional calendar fields (drill down capabilities, responsiveness to date related selections ...).

                   

                  hope this helps

                   

                  regards

                   

                  Marco

              • Re: Counting Days in Specific Month between Two Dates
                John Witherspoon

                Set analysis isn't responsive to the values of your dimensions in the way it seems you want here. You'll need another solution. Assuming your Start and End are values in some table that has some other key...

                 

                Name, Start, End
                Alice,1/2/2009,1/2/2009
                Bob  ,1/16/2009,3/27/2009
                Carla,2/3/2009,3/11/2009

                 

                ...a probably common way of doing it is to create a table that has one row for each key and each date between start and end for that key.

                 

                Name, Date
                Alice,1/2/2009
                Bob  ,1/16/2009
                Bob  ,1/17/2009
                ...
                Bob  ,3/26/2009
                Bob  ,3/27/2009
                Carla,2/3/2009
                Carla,2/4/2009
                ...
                Carla,3/10/2009
                Carla,3/11/2009

                 

                Connect your Date to a master calendar. Then your expression is just this:

                 

                count(distinct Date)

                  • Re: Counting Days in Specific Month between Two Dates
                    Aaron Xuereb

                    Hi John,

                     

                    Thanks for your reply.

                     

                    So what you are proposing is to loop through all the records in the table and create a new table with individual rows for all days between Start and End date? That sounds quite complicated to me. Is there a simple way to achieve this?

                     

                    Regards

                    Aaron

                      • Re: Counting Days in Specific Month between Two Dates
                        John Witherspoon

                        That is what I propose, yes, but it's simpler than I suspect you are imagining. See attached. Script and chart below.

                        Capture.PNG

                        Rentals:
                        LOAD *
                        ,recno() as RentalID
                        INLINE [
                        Apartment,Name,Start,End
                        217,Alice,1/2/2009,1/2/2009
                        217,Bob,1/16/2009,3/27/2009
                        105,Carla,2/3/2009,3/11/2009
                        ];

                        Occupancy:
                        LOAD
                        RentalID
                        ,Start+iterno()-1 as Date
                        RESIDENT Rentals
                        WHILE Start+iterno()-1 <= End
                        ;

                        Calendar:
                        LOAD *
                        ,monthstart(Date) as Month
                        ;
                        LOAD makedate(2008,12,31)+recno() as Date
                        AUTOGENERATE 90
                        ;

                         

                        The potential problem is memory. If you're tracking tens of thousands of apartments over the past 30 years, say, this would yield a very large table. It can be dramatically shrunk down if that's a problem, but doing so IS complicated. Maybe I should write a subroutine that builds the smaller but more complicated version automatically - give it a table, a key field, the Start and End fields, and the field name for the date it produces, and it builds the whole data structure and all the data for you. Something like that. Hmmm. Hmmm. Probably more time than I can devote right now, and you probably don't need it. It's hard for me to imagine you tracking enough apartments over a long enough time frame to cause a real problem.