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

Counting Days in Specific Month between Two Dates

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

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

Hi Marco,

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

Hi,

one way to do so might be:

```tabBookings:
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:
Num(DaysBooked/DaysInMonth,'0%') as Occupancy;
MonthName(MonthBookStart) as MonthBook,
MonthBookEnd-MonthBookStart+1 as DaysBooked,
MonthStart(MonthBookStart,1)-MonthStart(MonthBookStart) as DaysInMonth;
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

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

• Re: Counting Days in Specific Month between Two Dates

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

Hi John,

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

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

Rentals:
,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:
RentalID
,Start+iterno()-1 as Date
RESIDENT Rentals
WHILE Start+iterno()-1 <= End
;

Calendar:
,monthstart(Date) as Month
;
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

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