Count number of days for available rooms

[williamyuen96]

I'm building a dashboard for hotel purpose and I have this data set 



I having a problem here to come up with a formula how to calculate available room on the given date since there are many different people check out on a different date.

I tried Googling it and I found a solution where COUNTIFS is being used in excel. Anybody know how can I solve this in qlik sense measure ?

[kaanerisen]

Hi William,

The best practice for this to solve is handling in script level by definetly using IntervalMatch. If you have Total room count information, you can calculate available room count for specific date so easily.

Here is sample script (little bit modification on Vegar's sample script 🙂 )

Rooms:
load * Inline [
Room_ID,Number
1,101
2,102
3,103
];

Bookings:
LOAD 
	Guest,   date(date#(Checkin,'YYYY-MM-DD')) as Checkin,    date(num(date#(Checkout,'YYYY-MM-DD'))-1) as Checkout
inline [
  Guest,   Checkin,    Checkout
  Vegar,   2019-07-01, 2019-07-05
  William, 2019-07-02, 2019-07-04
  Lars,    2019-07-04, 2019-07-12
  Rob,     2019-07-07, 2019-07-11
  Lisa,    2019-07-04, 2019-07-05
];

// Master Calendar

      MinMax:
      Load min("Checkin") as MinDate,
           Max("Checkout") as MaxData
      Resident Bookings;

      let vMinDate=peek('MinDate',0,'MinMax');
      let vMaxDate=peek('MaxData',0,'MinMax');

      drop table MinMax;

      TempCalendar:
      load Date($(vMinDate)+IterNo()-1) as Date
      AutoGenerate(1) while $(vMinDate)+IterNo()-1 <= $(vMaxDate);

      MasterCalendar:
      Load  
                     Date AS "Date",  
                     week(Date) As Date.Week,  
                     Year(Date) As Date.Year,
                     'Q'&ceil(month(Date)/3) as Date.Quarter,
                     'Q'&ceil(month(Date)/3)&'-'& Year(Date) as Date.QuarterYear,
                     Month(Date) As Date.Month,  
                     Day(Date) As Date.Day,
                     date(monthstart(Date), 'MMM YYYY') as Date.MonthYear
      Resident TempCalendar  
      Order By Date ASC;

IntervalMatch (Date) 
LOAD 
  Checkin, Checkout
RESIDENT 
  Bookings
;

Dimension : Date

Reserved Room : Count(Guest)

Total Room : Count(Room_ID)

Available Room : Count(Room_ID) - Count(Guest)

Is this what you are looking for right?

Hope it helps...

[williamyuen96]

Hi there, could u do the same thing with this data I have provided ?  The calculation is wrong when i tried applied your script

[kaanerisen]

you can check attached qvf file. There is no data about the rooms in your dataset. You can't calculate available room count if you don't have data about rooms in dataset. 

# of Available Room = # of Room - # of Rezervations

Hope it helps..

[santosh4195]

Hello Kannersien,

I am also having same kind of problem. I have data set for both reserved and available rooms in same table. The available rooms having no start date or end date but the reserved rooms having start date and end date.  But how to show data of available/ reserved room in date, hour, minute?

Thanks in advance!!