topic Re: Counting Days in Specific Month between Two Dates in QlikView

Counting Days in Specific Month between Two Dates

Tue, 01 Nov 2016 09:14:44 GMT

Hello,

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

Any help would be greatly appreciated!

Thx

Re: Counting Days in Specific Month between Two Dates

MarcoWedel — Tue, 01 Nov 2016 22:55:59 GMT

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

johnw — Tue, 01 Nov 2016 23:23:42 GMT

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

Wed, 02 Nov 2016 14:49:07 GMT

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

Wed, 02 Nov 2016 14:51:37 GMT

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

johnw — Wed, 02 Nov 2016 16:43:37 GMT

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

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.

Re: Counting Days in Specific Month between Two Dates

MarcoWedel — Wed, 02 Nov 2016 22:22:28 GMT

Hi,

one way to do so might be:

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

Fri, 04 Nov 2016 07:57:24 GMT

Hi John,

I went for the solution you proposed and managed to get the result I was looking for! I only want to track around 50 apartments so I am sure I will not be running into memory problems!

Many thanks for your help with this!

/Aaron

Re: Counting Days in Specific Month between Two Dates

Fri, 04 Nov 2016 07:58:36 GMT

Hi Marco,

Thanks for your reply. As you also suggested I went with John's solution I got the results I was looking for. I would like to thank you for your support with this issue!

Regards

Aaron